You are correct, "" isn't the same a blank. If column B contains numbers
except for the apparent blank cells, the you can differentiate number and
text

Dim rng as Range
Dim rng1 as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlConstants,xlTextValues)
set rng1 = Intersect(rng.EntireRow,Columns("A:B"))
rng1.Delete shift:=xlShiftUp
On Error goto 0

obviously test this on a copy of your data to see if it is deleting the
correct rows.

--
Regards,
Tom Ogilvy




"Bri" wrote:

Tom - thanks for your post

The code you wrote works perfectly on 'mock' data that I used to test it.
But... in my actual worksheet, it doesn't get rid of the blank rows. I
think its because all of the cells including the blank ones resulted from a
paste action using:

rng.PasteSpecial Paste:=xlPasteValues

I don't see anything in these 'blank' cells (their pasted value is "" from
the formuls
'=IF(code here,"",more code)' ) but when I select then delete
these cells, your code works perfectly. Its almost as if "" isn't the same
as blank.

any ideas?
TQS, Bri

"Tom Ogilvy" wrote in message
...
Dim rng as Range
Dim rng1 as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlblanks)
set rng1 = Intersect(rng.EntireRow,Columns("A:B"))
rng1.Delete shift:=xlShiftUp
On Error goto 0

--
Regards,
Tom Ogilvy


"Bri" wrote in message
...
This one has me stymied. My worksheet collects data in columns A and B
corresponding to ID and MonSales. This is repeated in columns C and D
corresponding to ID and TueSales, and so on. Repeats of values in any
column are allowed.

My problem is that there are many instances where a row has no value of
A
or B. I have code that sorts A:B by A ascending, then B descending, but
these 'blanks' go to the top of the sort. I don't know how to get rid of
them. Deleting rows doesn't work because it interferes with the data in
C:D, E:F, etc. Needless to say, this sorting needs to be done for each
column pair.

I would very much appreciate help with this.
Thanks - Bri