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