help with macro and delete rows

[RichardO]

Hello all:

In my data, column Q has a vlookup formula. If the result is N/A, th
formula puts a blank in the cell.

I recorded a macro and among other things that it does, it autofilter
the data by column Q and by the blank. Then I select all the row 2 t
109 that the autofilter resulted and then delete them. My problem is
some time Q2 might not be a blank cell, and Q109 might not be the las
cell with a blank.

This is the macro I recorded:

Range("O1").Select
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=6
Selection.AutoFilter Field:=17, Criteria1:="="
Rows("2:109").Select
Range("G2").Activate
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("K3").Select


I thought that if I found the last row of the data and then do th
autofilter, maybe then it would capture all the cells that need to b
deleted, see my macro below. But I am getting a debug error with thi
macro

Range("b1").Select
Selection.End(xlDown).Select
ro$ = ActiveCell.Row
Selection.AutoFilter
Selection.AutoFilter Field:=17, Criteria1:="="
Rows("a2:q" & ro$).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("O1").Select


Thanks for helping out.

Richardo

 

[RichardO]

I forgot to mention that row 1 is a header.

Thanks

Richard