The following piece of code errors out at the line indicated:

Set rTtl = ActiveSheet.Range("A10:AE10")


For Each rCell In rTtl

rCell.Select

If rCell = "" Then

rCell.EntireColumn.Delete
rCell.Offset(0, -1).Activate<---OBJECT REQUIRED

Else: rCell.EntireColumn.AutoFit
End If
Next

Can anyone tell me what it's looking for? I've tried both "activate"
and "select", but I get the same error.
The program goes across row 10, checking each cell for content. If the
cell is blank, it deletes the column. The problem comes when it deletes
the column. If the column to the right is also blank, it skips over it
when it comes to the "Next" line. I want it to test and, if necessary
delete that column.
Thanks!

Try:

Sub DelCols()
Dim Col As Long
For Col = 31 To 1 Step -1
With Cells(10, Col)
If .Value = Empty Then
.EntireColumn.Delete
Else
.EntireColumn.AutoFit
End If
End With
Next Col
End Sub

Hope this helps
Rowan

davegb wrote:
The following piece of code errors out at the line indicated:

Set rTtl = ActiveSheet.Range("A10:AE10")


For Each rCell In rTtl

rCell.Select

If rCell = "" Then

rCell.EntireColumn.Delete
rCell.Offset(0, -1).Activate<---OBJECT REQUIRED

Else: rCell.EntireColumn.AutoFit
End If
Next

Can anyone tell me what it's looking for? I've tried both "activate"
and "select", but I get the same error.
The program goes across row 10, checking each cell for content. If the
cell is blank, it deletes the column. The problem comes when it deletes
the column. If the column to the right is also blank, it skips over it
when it comes to the "Next" line. I want it to test and, if necessary
delete that column.
Thanks!

Just to add to Rowan's post...

These two lines don't play nice:

Set rTtl = ActiveSheet.Range("A10:AE10")
and later...
rCell.Offset(0, -1).Activate

When rCell is A10, then rcell.offset(0,-1) is one column to the left of column
A--and that causes trouble.


And the way Rowan suggested (start in the right most column and work toward the
left) makes keeping track of things pretty easy.

But here's another way:

dim delRng as range
dim rTtl as range
dim rCell as range
dim rTtl = activesheet.Range("a10:AE10")

for each rcell rttl.cells
if rcell.value = "" then
if delrng is nothing then
set delrng = rcell
else
set delrng = union(rcell,delrng)
end if
else
rcell.entirecolumn.autofit
end if
next rcell

if delrng is nothing then
'nothing to delete
else
delrng.entirecolumn.delete
end if

============

And since you're not really doing anything in that loop that depends on the
..activate, it wouldn't even be necessary. (Although, just removing that line
won't be sufficient in this situation.)




davegb wrote:

The following piece of code errors out at the line indicated:

Set rTtl = ActiveSheet.Range("A10:AE10")

For Each rCell In rTtl

rCell.Select

If rCell = "" Then

rCell.EntireColumn.Delete
rCell.Offset(0, -1).Activate<---OBJECT REQUIRED

Else: rCell.EntireColumn.AutoFit
End If
Next

Can anyone tell me what it's looking for? I've tried both "activate"
and "select", but I get the same error.
The program goes across row 10, checking each cell for content. If the
cell is blank, it deletes the column. The problem comes when it deletes
the column. If the column to the right is also blank, it skips over it
when it comes to the "Next" line. I want it to test and, if necessary
delete that column.
Thanks!

--

Dave Peterson

Dave Peterson wrote:
Just to add to Rowan's post...

These two lines don't play nice:

Set rTtl = ActiveSheet.Range("A10:AE10")
and later...
rCell.Offset(0, -1).Activate

When rCell is A10, then rcell.offset(0,-1) is one column to the left of column
A--and that causes trouble.


And the way Rowan suggested (start in the right most column and work toward the
left) makes keeping track of things pretty easy.

But here's another way:

dim delRng as range
dim rTtl as range
dim rCell as range
dim rTtl = activesheet.Range("a10:AE10")

for each rcell rttl.cells
if rcell.value = "" then
if delrng is nothing then
set delrng = rcell
else
set delrng = union(rcell,delrng)
end if
else
rcell.entirecolumn.autofit
end if
next rcell

if delrng is nothing then
'nothing to delete
else
delrng.entirecolumn.delete
end if


Dave,
Thanks for your reply. I don't understand how this macro works at all.
I copied it to the VBE and, with a couple of adjustments, it works
great.

Set rTtl = ActiveSheet.Range("a10:AE10")

For Each rCell In rTtl.Cells
rCell.Select

If rCell.Value = "" Then
If delRng Is Nothing Then
'delRng.Select

Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
Else
rCell.EntireColumn.AutoFit
End If
Next rCell

If delRng Is Nothing Then
'nothing to delete
Else
delRng.EntireColumn.Delete
End If
End Sub

But where is delrng? Nothing tells the macro where delrng is, so what
does it assume? Is there some standard assumption about where a range
is if it's not defined? Notice I put a "delrng.select" in to see where
delrng is, but it errored out on me (object variable or withblock
variable not set), which is what I'd expect. So why does the macro even
run if delrng is undetermined?



============

And since you're not really doing anything in that loop that depends on the
.activate, it wouldn't even be necessary. (Although, just removing that line
won't be sufficient in this situation.)




davegb wrote:

The following piece of code errors out at the line indicated:

Set rTtl = ActiveSheet.Range("A10:AE10")

For Each rCell In rTtl

rCell.Select

If rCell = "" Then

rCell.EntireColumn.Delete
rCell.Offset(0, -1).Activate<---OBJECT REQUIRED

Else: rCell.EntireColumn.AutoFit
End If
Next

Can anyone tell me what it's looking for? I've tried both "activate"
and "select", but I get the same error.
The program goes across row 10, checking each cell for content. If the
cell is blank, it deletes the column. The problem comes when it deletes
the column. If the column to the right is also blank, it skips over it
when it comes to the "Next" line. I want it to test and, if necessary
delete that column.
Thanks!

--

Dave Peterson

I meant to comment in my first reply. This is pretty sneaky, going
backward to avoid the problem I encountered! Gonna keep a close eye on
you, Rowan :)
Actually, it's pretty clever.

Rowan Drummond wrote:
Try:

Sub DelCols()
Dim Col As Long
For Col = 31 To 1 Step -1
With Cells(10, Col)
If .Value = Empty Then
.EntireColumn.Delete
Else
.EntireColumn.AutoFit
End If
End With
Next Col
End Sub

Hope this helps
Rowan

davegb wrote:
The following piece of code errors out at the line indicated:

Set rTtl = ActiveSheet.Range("A10:AE10")


For Each rCell In rTtl

rCell.Select

If rCell = "" Then

rCell.EntireColumn.Delete
rCell.Offset(0, -1).Activate<---OBJECT REQUIRED

Else: rCell.EntireColumn.AutoFit
End If
Next

Can anyone tell me what it's looking for? I've tried both "activate"
and "select", but I get the same error.
The program goes across row 10, checking each cell for content. If the
cell is blank, it deletes the column. The problem comes when it deletes
the column. If the column to the right is also blank, it skips over it
when it comes to the "Next" line. I want it to test and, if necessary
delete that column.
Thanks!

Thanks, Rowan!
Rowan Drummond wrote:
Try:

Sub DelCols()
Dim Col As Long
For Col = 31 To 1 Step -1
With Cells(10, Col)
If .Value = Empty Then
.EntireColumn.Delete
Else
.EntireColumn.AutoFit
End If
End With
Next Col
End Sub

Hope this helps
Rowan

davegb wrote:
The following piece of code errors out at the line indicated:

Set rTtl = ActiveSheet.Range("A10:AE10")


For Each rCell In rTtl

rCell.Select

If rCell = "" Then

rCell.EntireColumn.Delete
rCell.Offset(0, -1).Activate<---OBJECT REQUIRED

Else: rCell.EntireColumn.AutoFit
End If
Next

Can anyone tell me what it's looking for? I've tried both "activate"
and "select", but I get the same error.
The program goes across row 10, checking each cell for content. If the
cell is blank, it deletes the column. The problem comes when it deletes
the column. If the column to the right is also blank, it skips over it
when it comes to the "Next" line. I want it to test and, if necessary
delete that column.
Thanks!

delRng was declared as a range variable.

And it's actually being built as the code runs.

If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If

If it's nothing, then put the first rCell (that meets your criteria ="") into
that range.

If it's already got some cells in it, then keep adding the next rCell to that
evergrowing range. That's what Union does.

If you really want to select it, you can do it where you would have been
deleting:


If delRng Is Nothing Then
'nothing to delete
Else
delRng.EntireColumn.select
'or
delRng.select
End If



davegb wrote:

Set rTtl = ActiveSheet.Range("a10:AE10")

For Each rCell In rTtl.Cells
rCell.Select

If rCell.Value = "" Then
If delRng Is Nothing Then
'delRng.Select

Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
Else
rCell.EntireColumn.AutoFit
End If
Next rCell

If delRng Is Nothing Then
'nothing to delete
Else
delRng.EntireColumn.Delete
End If
End Sub

But where is delrng? Nothing tells the macro where delrng is, so what
does it assume? Is there some standard assumption about where a range
is if it's not defined? Notice I put a "delrng.select" in to see where
delrng is, but it errored out on me (object variable or withblock
variable not set), which is what I'd expect. So why does the macro even
run if delrng is undetermined?

============

And since you're not really doing anything in that loop that depends on the
.activate, it wouldn't even be necessary. (Although, just removing that line
won't be sufficient in this situation.)




davegb wrote:

The following piece of code errors out at the line indicated:

Set rTtl = ActiveSheet.Range("A10:AE10")

For Each rCell In rTtl

rCell.Select

If rCell = "" Then

rCell.EntireColumn.Delete
rCell.Offset(0, -1).Activate<---OBJECT REQUIRED

Else: rCell.EntireColumn.AutoFit
End If
Next

Can anyone tell me what it's looking for? I've tried both "activate"
and "select", but I get the same error.
The program goes across row 10, checking each cell for content. If the
cell is blank, it deletes the column. The problem comes when it deletes
the column. If the column to the right is also blank, it skips over it
when it comes to the "Next" line. I want it to test and, if necessary
delete that column.
Thanks!

--

Dave Peterson

--

Dave Peterson

ps. Sorry about the typo!

davegb wrote:

Dave Peterson wrote:
Just to add to Rowan's post...

These two lines don't play nice:

Set rTtl = ActiveSheet.Range("A10:AE10")
and later...
rCell.Offset(0, -1).Activate

When rCell is A10, then rcell.offset(0,-1) is one column to the left of column
A--and that causes trouble.


And the way Rowan suggested (start in the right most column and work toward the
left) makes keeping track of things pretty easy.

But here's another way:

dim delRng as range
dim rTtl as range
dim rCell as range
dim rTtl = activesheet.Range("a10:AE10")

for each rcell rttl.cells
if rcell.value = "" then
if delrng is nothing then
set delrng = rcell
else
set delrng = union(rcell,delrng)
end if
else
rcell.entirecolumn.autofit
end if
next rcell

if delrng is nothing then
'nothing to delete
else
delrng.entirecolumn.delete
end if


Dave,
Thanks for your reply. I don't understand how this macro works at all.
I copied it to the VBE and, with a couple of adjustments, it works
great.

Set rTtl = ActiveSheet.Range("a10:AE10")

For Each rCell In rTtl.Cells
rCell.Select

If rCell.Value = "" Then
If delRng Is Nothing Then
'delRng.Select

Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
Else
rCell.EntireColumn.AutoFit
End If
Next rCell

If delRng Is Nothing Then
'nothing to delete
Else
delRng.EntireColumn.Delete
End If
End Sub

But where is delrng? Nothing tells the macro where delrng is, so what
does it assume? Is there some standard assumption about where a range
is if it's not defined? Notice I put a "delrng.select" in to see where
delrng is, but it errored out on me (object variable or withblock
variable not set), which is what I'd expect. So why does the macro even
run if delrng is undetermined?

============

And since you're not really doing anything in that loop that depends on the
.activate, it wouldn't even be necessary. (Although, just removing that line
won't be sufficient in this situation.)




davegb wrote:

The following piece of code errors out at the line indicated:

Set rTtl = ActiveSheet.Range("A10:AE10")

For Each rCell In rTtl

rCell.Select

If rCell = "" Then

rCell.EntireColumn.Delete
rCell.Offset(0, -1).Activate<---OBJECT REQUIRED

Else: rCell.EntireColumn.AutoFit
End If
Next

Can anyone tell me what it's looking for? I've tried both "activate"
and "select", but I get the same error.
The program goes across row 10, checking each cell for content. If the
cell is blank, it deletes the column. The problem comes when it deletes
the column. If the column to the right is also blank, it skips over it
when it comes to the "Next" line. I want it to test and, if necessary
delete that column.
Thanks!

--

Dave Peterson

--

Dave Peterson

Dave Peterson wrote:
delRng was declared as a range variable.

And it's actually being built as the code runs.

If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If

If it's nothing, then put the first rCell (that meets your criteria ="") into
that range.

If it's already got some cells in it, then keep adding the next rCell to that
evergrowing range. That's what Union does.

If you really want to select it, you can do it where you would have been
deleting:


If delRng Is Nothing Then
'nothing to delete
Else
delRng.EntireColumn.select
'or
delRng.select
End If



davegb wrote:

Set rTtl = ActiveSheet.Range("a10:AE10")

For Each rCell In rTtl.Cells
rCell.Select

If rCell.Value = "" Then
If delRng Is Nothing Then
'delRng.Select

Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
Else
rCell.EntireColumn.AutoFit
End If
Next rCell

If delRng Is Nothing Then
'nothing to delete
Else
delRng.EntireColumn.Delete
End If
End Sub

But where is delrng? Nothing tells the macro where delrng is, so what
does it assume? Is there some standard assumption about where a range
is if it's not defined? Notice I put a "delrng.select" in to see where
delrng is, but it errored out on me (object variable or withblock
variable not set), which is what I'd expect. So why does the macro even
run if delrng is undetermined?

============

And since you're not really doing anything in that loop that depends on the
.activate, it wouldn't even be necessary. (Although, just removing that line
won't be sufficient in this situation.)




davegb wrote:

The following piece of code errors out at the line indicated:

Set rTtl = ActiveSheet.Range("A10:AE10")

For Each rCell In rTtl

rCell.Select

If rCell = "" Then

rCell.EntireColumn.Delete
rCell.Offset(0, -1).Activate<---OBJECT REQUIRED

Else: rCell.EntireColumn.AutoFit
End If
Next

Can anyone tell me what it's looking for? I've tried both "activate"
and "select", but I get the same error.
The program goes across row 10, checking each cell for content. If the
cell is blank, it deletes the column. The problem comes when it deletes
the column. If the column to the right is also blank, it skips over it
when it comes to the "Next" line. I want it to test and, if necessary
delete that column.
Thanks!

--

Dave Peterson

--

I tried to duplicate what you did with the columns to remove empty
rows. But "union" is failing.

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)<---METHOD UNION FAILS
End If
End If
Next rCell

If rngDel Is Nothing Then
Else
rngDel.EntireRow.Delete
End If

End Sub

Does Union work only across rows? Is there a similar command for
columns?
Thanks again!

Dave Peterson

Your code worked fine for me after I declared all the variables.

Option Explicit
Sub testme02()
Dim RngCol As Range
Dim rCell As Range
Dim rngDel As Range
Dim lRow As Long

With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Set RngCol = ActiveSheet.Range("A11:A" & lRow)
For Each rCell In RngCol.Cells
If rCell.value = "" 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
'do nothing
Else
rngDel.EntireRow.Delete
End If

End Sub

I don't see another reason why the union would be failing.

How did you declare your variables?


davegb wrote:

<<snipped

I tried to duplicate what you did with the columns to remove empty
rows. But "union" is failing.

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)<---METHOD UNION FAILS
End If
End If
Next rCell

If rngDel Is Nothing Then
Else
rngDel.EntireRow.Delete
End If

End Sub

Does Union work only across rows? Is there a similar command for
columns?
Thanks again!

Dave Peterson

--

Dave Peterson

Dave Peterson wrote:
Your code worked fine for me after I declared all the variables.

Option Explicit
Sub testme02()
Dim RngCol As Range
Dim rCell As Range
Dim rngDel As Range
Dim lRow As Long

With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Set RngCol = ActiveSheet.Range("A11:A" & lRow)
For Each rCell In RngCol.Cells
If rCell.value = "" 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
'do nothing
Else
rngDel.EntireRow.Delete
End If

End Sub

I don't see another reason why the union would be failing.

How did you declare your variables?

I copied the macro and the declarations into a separate module, then
ran it and it worked fine, just like yours did. So it must be something
about the first part of the macro. Here's the entire macro:

Dim Wksht As Worksheet
Dim lRow As Long
Dim rPay As Range
Dim rCell As Range
Dim rTtl As Range
Dim rRng As Range
Dim rngCol As Range
Dim rngDel As Range

Call Clean
lRow = 1000
Set Wksht = ActiveSheet
Wksht.Cells.RowHeight = 12.75

Wksht.Range("A8", "AE" & lRow).Select

Selection.UnMerge
Range("A10") = "Service County"

Range("B10") = "Service Provider Name"
Range("G10") = "Prov Id"
Range("H10") = "Lic Cert Type"
Range("I10") = "Effective Date"
Range("J10") = "Close Date"
Range("K10") = "Srvc Type"
Range("L10") = "Srvc Appr Status"
Range("O10") = "Gov Body Id"
Range("P10") = "Client Id"
Range("Q10") = "Client Last Name"
Range("R10") = "Client First Name"
Range("T10") = "Client State Id"
Range("U10") = "Client Srvc Begin Dt"
Range("V10") = "Client Srvd End Dt"
Range("W10") = "Pay Prvdr Y or N"
Range("Z10") = "IVE Entitlement Type"
Range("AC10") = "IVE Start Date"
Range("AE10") = "IVE End Date"

Range("W11").Activate


Set rPay = ActiveSheet.Range("W11", Cells(lRow, "W"))

For Each rCell In rPay
'rCell.Select

If rCell < "" Then
If rCell.Offset(0, -2) = "" Then

Range(rCell, rCell.Offset(0, 8)).Cut
Destination:=rCell.Offset(-1, 0)

End If
End If
Next

Range("F11:F" & lRow).Cut Destination:=Range("F11:F" & lRow).Offset(0,
1)
Range("AB11:AB" & lRow).Cut Destination:=Range("AB11:AB" &
lRow).Offset(0, 1)




Set rTtl = ActiveSheet.Range("a10:AE10")

For Each rCell In rTtl.Cells

If rCell.Value = "" Then
If rngDel Is Nothing Then

Set rngDel = rCell
Else
Set rngDel = Union(rCell, rngDel)

End If
Else
rCell.EntireColumn.AutoFit
End If
Next rCell

If rngDel Is Nothing Then
'nothing to delete
Else
rngDel.EntireColumn.Delete
End If

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
'nothing to delete

Else
rngDel.EntireRow.Delete
End If

End Sub

Do you see what's causing the union to fail?

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

"davegb" wrote in message
oups.com...

Dave Peterson wrote:
delRng was declared as a range variable.

And it's actually being built as the code runs.

If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If

If it's nothing, then put the first rCell (that meets your criteria ="")
into
that range.

If it's already got some cells in it, then keep adding the next rCell to
that
evergrowing range. That's what Union does.

If you really want to select it, you can do it where you would have been
deleting:


If delRng Is Nothing Then
'nothing to delete
Else
delRng.EntireColumn.select
'or
delRng.select
End If



davegb wrote:

Set rTtl = ActiveSheet.Range("a10:AE10")

For Each rCell In rTtl.Cells
rCell.Select

If rCell.Value = "" Then
If delRng Is Nothing Then
'delRng.Select

Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
Else
rCell.EntireColumn.AutoFit
End If
Next rCell

If delRng Is Nothing Then
'nothing to delete
Else
delRng.EntireColumn.Delete
End If
End Sub

But where is delrng? Nothing tells the macro where delrng is, so what
does it assume? Is there some standard assumption about where a range
is if it's not defined? Notice I put a "delrng.select" in to see where
delrng is, but it errored out on me (object variable or withblock
variable not set), which is what I'd expect. So why does the macro
even
run if delrng is undetermined?

============

And since you're not really doing anything in that loop that depends
on the
.activate, it wouldn't even be necessary. (Although, just removing
that line
won't be sufficient in this situation.)




davegb wrote:

The following piece of code errors out at the line indicated:

Set rTtl = ActiveSheet.Range("A10:AE10")

For Each rCell In rTtl

rCell.Select

If rCell = "" Then

rCell.EntireColumn.Delete
rCell.Offset(0, -1).Activate<---OBJECT REQUIRED

Else: rCell.EntireColumn.AutoFit
End If
Next

Can anyone tell me what it's looking for? I've tried both
"activate"
and "select", but I get the same error.
The program goes across row 10, checking each cell for content. If
the
cell is blank, it deletes the column. The problem comes when it
deletes
the column. If the column to the right is also blank, it skips
over it
when it comes to the "Next" line. I want it to test and, if
necessary
delete that column.
Thanks!

--

Dave Peterson

--

I tried to duplicate what you did with the columns to remove empty
rows. But "union" is failing.

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)<---METHOD UNION FAILS
End If
End If
Next rCell

If rngDel Is Nothing Then
Else
rngDel.EntireRow.Delete
End If

End Sub

Does Union work only across rows? Is there a similar command for
columns?
Thanks again!

Dave Peterson

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!

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")

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!

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!