excel - VBA code to delete range based on variable - running slowly -

i'm using code delete range, cell in last column (aa) of range equal variable specified elsewhere in worksheet (k2).

the code uses shift: xlup remove data , shift rest of data upwards.

all of done 1 row @ time until condition no longer true.

the issue runs quite - 25-30 rows of data being deleted, 1 @ time. can speed up?

sub uncommitsession() dim what_to_find string dim ws excel.worksheet dim foundcell excel.range dim ival integer  ival = application.worksheetfunction.countif(range("aa5:aa800"), range("k2"))  what_to_find = range("k2")  = 1 ival   set ws = activesheet   set foundcell = ws.range("aa:aa").find(what:=what_to_find, lookat:=xlwhole)   if not foundcell nothing     range("q" & foundcell.row & ":aa" & foundcell.row).delete shift:=xlup   else     msgbox (what_to_find & " not found in session archive.")   end if next end sub 

i think fastest solution set autofilters, select visible rows , delete them. here sample 1 of projects, uses auto filter through list objects:

dim lsttable listobject: set lsttable = rngtarget.worksheet.listobjects.add(xlsrcrange, rngtarget, , xlyes) lsttable.range.autofilter colfilter, varvalue lsttable.databodyrange.entirerow.delete 

in above need change rngtarget refer table, colfilter refer column number of aa , varvalue what_to_find.

update

customizing code:

sub uncommitsession()     dim ewstarget worksheet: set ewstarget = activesheet     dim varvalue string: varvalue = ewstarget.range("k2").value     dim rngtarget range: set rngtarget = ewstarget.range("a5:aa800")     dim lsttable listobject: set lsttable = rngtarget.worksheet.listobjects.add(xlsrcrange, rngtarget, , xlyes)     lsttable.range.autofilter ewstarget.range("aa1") - ewstarget.range("a1") + 1, varvalue     lsttable.tablestyle = vbnullstring     lsttable.databodyrange.entirerow.delete     lsttable.unlist end sub 

i don't have, however, workbook, cannot test it.