Object required?
Tom Ogilvy wrote:
Union works with rows, columns, single cells, multiple cells, discontiguous
ranges, you name it.
for example, this ran fine for me:
Sub BBCCDD()
Dim rngDel As Range, rCell As Range
Dim rngCol As Range
Dim lRow As Long
lRow = 30
Set rngCol = ActiveSheet.Range("A11:A" & lRow)
For Each rCell In rngCol.Cells
If rCell = "" Then
If rngDel Is Nothing Then
Set rngDel = rCell
Else
Set rngDel = Union(rCell, rngDel)
End If
End If
Next rCell
If rngDel Is Nothing Then
Else
rngDel.EntireRow.Delete
End If
End Sub
Assume you don't have merged cells or anything like that.
--
Regards,
Tom Ogilvy
Thanks for your reply, Tom
No merged cells. But I finally got the macro to run when I changed the
rngDel variable into 2 variables. Used rngDel for the first part of the
macro to remove blank columns, then used rng2Del for the second part of
the macro to remove blank rows. Works fine now. Somehow, I guess, the 2
ranges were being confused, even though it appeared to me that I had
reset the variable at the beginning of the second part of the code.
Thanks to all for the help!
|