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