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!

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!

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

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

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

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

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