Rowan Drummond wrote:
Hi Dave

Your problem was that you were setting rngDel and then deleteing all the
columns which meant that rngDel referred to cells that no longer existed.
You were then going on to attempt to add more cells to rngDel. The
easiest fix would be to insert
Set rngDel = nothing
before you start processing the rows.

Regards
Rowan

PS have you tried

Range("G10:L10") = Array("Prov Id", "Lic Cert Type", _
"Effective Date", "Close Date", "Srvc Type", _
"Srvc Appr Status")

Thanks for the info, Rowan. Now it makes sense.
Thanks for the tip on the array, I'm sure I'll be able to use that
soon.


davegb wrote:
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!