i need help with error on deleting rows in vba

[GregJG]

currently I am using this code;

Set SourceWB = Workbooks("book1.xls")
With SourceWB.Sheets("sheet1")
Set rnrange = .Range("g11:g30")
Set rnrange = rnrange.SpecialCells(xlBlanks)
rnrange.EntireRow.Delete
End With

the error I am getting is

run time error '1004':
no cells were found

g11:g20 are filled, it should be deleting rows 21 thru 30?

 

[Dave Peterson]

One way to avoid the error:

Dim SourceWB As Workbook
Dim rnrange As Range

Set SourceWB = Workbooks("book1")
With SourceWB.Sheets("sheet1")
Set rnrange = .Range("g11:g30")
On Error Resume Next
Set rnrange = rnrange.SpecialCells(xlBlanks)
on Error goto 0
if rnrange is nothing then
msgbox "no blanks found"
else
rnrange.EntireRow.Delete
end if
End With

Specialcells will only look at the usedrange of the worksheet--so if you've
never touched rows 21 to 65536, then G21:G30 won't even be looked at. (Hit
ctrl-end to see where excel thinks the last used cell is. If it's after row 20,
then this isn't the problem.)

Another problem is that those cells may not be really empty. If you go back to
the worksheet and put:

=counta(g21:g30)

If those cells are really empty, then you should see this formula evaluate to 0.

If you see a positive number appear, then you've got something in those cells.

And formulas that evaluate to "" are not empty.
A cell with =if(a21="x","ok","")
is not empty even if it looks empty.

And if you had a formula that evaluated to "" and then did edit|copy,
edit|paste special|values, then these cells aren't empty, either.