excel - Deleting or keeping multiple rows by a specific word content -

i'm trying write code either deletes or keeps rows specific word input end-user.

i've created 2 button actions:

sub button1_click() dim cell range word1 = inputbox("enter word want keep rows", "enter") each cell in selection     cell.entirerow.hidden = (instr(1, cell, word1, 1) = 0) 'keep word input user next end sub  sub button2_click() dim cell range word2 = inputbox("enter word want delete rows", "enter") each cell in selection     cell.entirerow.hidden = (instr(1, cell, word2, 1) = 1) 'delete word input user next end sub 

however, these buttons don't work quite way them do.

problems:

1) have select cells in column of text searched; if select whole block of data,everything deleted.

2) actually, program handier, if did magic cell j22 onwards (to right , downwards) until end of data reached, without need select anything. best way this?

3) if use these buttons several times sequentially, rows i've deleted keep popping again. how make delete "permanent" each time use 1 of these buttons? changing hidden delete start run-time errors.

1. with current code, if select whole block of data, checks each cell in selection individually , acts accordingly. if have range selected a1:j1,000, hide every row unless each cell in every row of selection contains input word.
2. depending on want, try sheets("sheet1").cells(sheets("sheet1").rows.count, 10).end(xlup).row returns rownumber of last cell in column 10(j), more examples of in code below
3. this caused loop , deletion of rows, for = 1 100 check cells a1 a100, if delete row during loop, loop still continue 100 , not end @ 99, end of loop set before loop starts , not change during loop. more information on , it's solutions here.

general

• avoid .select/.activate methods , .selection property, source of many bugs.
• declare variables, use option explicit enforce this.

here refactored code annotations.

option explicit  sub button1_click() 'keep rows based on input       'declaration of variables     dim long     dim strfilterword string     dim rngcell range     dim rngtodelete range, rngrow range     dim arrrow() variant, arrtmp() variant      'setting filter word     strfilterword = inputbox("enter word want keep rows", "enter")      thisworkbook.worksheets("sheet1") 'replace "sheet1" actual name of sheet.         'setting loop, range loop on j22:j(lastrow data)         each rngcell in .range(.cells(22, 10), .cells(rows.count, 10).end(xlup))             'all values of current row combined array              'determining , setting range of current row             set rngrow = rngcell.resize(1, 3)             'populate tmp array row range values             arrtmp = rngrow              'to use array, needs 1d, 2d, section below accomplishes             'resize final array             redim arrrow(lbound(arrtmp, 2) ubound(arrtmp, 2))             'copy values final array             = lbound(arrtmp, 2) ubound(arrtmp, 2)                 arrrow(i) = arrtmp(1, i)             next              'the final array combined single string value " "(spaces) between each array element             'if filterword not found in string instr returns 0             'if filterword found in string instr returns number corresponding start position.             if instr(1, join(arrrow, " "), strfilterword, vbtextcompare) = 0                 'test see if range delete empty or not                 if rngtodelete nothing                     'if range empty, set first row delete.                     set rngtodelete = rngcell.entirerow                 else                     'if range not empty, row delete added range.                     set rngtodelete = union(rngtodelete, rngcell.entirerow)                 end if             end if         next rngcell          'after cells looped over, rows delete deleted in 1 go         if not rngtodelete nothing rngtodelete.delete     end  end sub  sub button2_click() 'keep rows based on input       'declaration of variables     dim long     dim strfilterword string     dim rngcell range     dim rngtodelete range, rngrow range     dim arrrow() variant, arrtmp() variant      'setting filter word     strfilterword = inputbox("enter word want delete rows", "enter")      thisworkbook.worksheets("sheet1") 'replace "sheet1" actual name of sheet.         'setting loop, range loop on j22:j(lastrow data)         each rngcell in .range(.cells(22, 10), .cells(rows.count, 10).end(xlup))             'all values of current row combined array              'determining , setting range of current row             set rngrow = rngcell.resize(1, 3)             'populate tmp array row range values             arrtmp = rngrow              'to use array, needs 1d, 2d, section below accomplishes             'resize final array             redim arrrow(lbound(arrtmp, 2) ubound(arrtmp, 2))             'copy values final array             = lbound(arrtmp, 2) ubound(arrtmp, 2)                 arrrow(i) = arrtmp(1, i)             next              'the final array combined single string value " "(spaces) between each array element             'if filterword not found in string instr returns 0             'if filterword found in string instr returns number corresponding start position.             if instr(1, join(arrrow, " "), strfilterword, vbtextcompare) > 0                 'test see if range delete empty or not                 if rngtodelete nothing                     'if range empty, set first row delete.                     set rngtodelete = rngcell.entirerow                 else                     'if range not empty, row delete added range.                     set rngtodelete = union(rngtodelete, rngcell.entirerow)                 end if             end if         next rngcell          'after cells looped over, rows delete deleted in 1 go         if not rngtodelete nothing rngtodelete.delete     end  end sub