You'll want to change this line:
iLastCol = Cells.SpecialCells(xlCellTypeLastCell).Column
to
iLastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column

This extra dot means that excel will use the correct worksheet
(With Worksheets("2006 Realized Gains"))

Another way is if you can pick out a row that always has data:

With Worksheets("2006 Realized Gains")
iLastCol = .cells(1,.columns.count).end(xltoleft).column
...

I used row 1 to determine the last used column.

==
Sometimes excel's lastusedcell won't be what you expect it to be.

Or visit Debra Dalgleish's site for some techniques for resetting that
lastusedcell.
http://www.contextures.com/xlfaqApp.html#Unused




Dallman Ross wrote:

Dave Peterson suppled a nice, easy macro for me last week.
I turned it into a toggle button, which I like. I asked
a follow-up about finding the last column in the VBA code
rather than hard-coding a number. (I'd used 30 as the max
column value in my example, and Dave had gone with that.)

I'm certain this is entirely easy for the experienced VBA coders,
but because my VBA-fu is weak, it left me baffled. I
left things for a few days with the hard-coded 30. Occasionally
I would try to emend the code myself, but my efforts proved
fruitless. Well, today I succeeded. It's not hard, but
I just needed the right syntax.

Option Explicit
Private Sub ToggleButton1_Click()

Dim iCol As Long

'I just added this:
Dim iLastCol As Long

With Worksheets("2006 Realized Gains")

'and I just added this:
iLastCol = Cells.SpecialCells(xlCellTypeLastCell).Column

'and in the next line, now use "iLastCol" instead of "30":
For iCol = 1 To iLastCol

If .Cells(2, iCol).Font.ColorIndex = 5 _
And .Cells(2, iCol).Interior.ColorIndex = 24 Then
.Columns(iCol).Hidden = ToggleButton1.Value
Else
.Columns(iCol).Hidden = False
End If
Next iCol

'this is just for looks when I'm done
Range("A1").Select

End With
End Sub

(Thanks again, Dave!)

I found the needed syntax via Google, he
http://www.parry.co.nz/findlastrowor...%20a%20 Sheet

Dallman

=============================================
On 15 October 2006 in , Dallman
Ross <dman@localhost. spake thusly:

In response to my original --

Short version: I want a macro to hide columns of a certain
cell color and font color.

Dave Peterson had replied as follows:

Option Explicit
Sub testme()

Dim iCol As Long

With Worksheets("sheet1")
For iCol = 1 To 30
If .Cells(1, iCol).Font.ColorIndex = 6 _
And .Cells(1, iCol).Interior.ColorIndex = 35 Then
.Columns(iCol).Hidden = True
Else
.Columns(iCol).Hidden = False
End If
Next iCol
End With
End Sub


As I posted already, this is great and solved my problem. Now
I'm trying to take it a bit further. This is an early foray
for me into the world of VBA macros. I'm stuck on a couple of
new ideas.

Okay, I decided it would be cool to make a button (control box)
to toggle my hidden fields on and off. I'd never made a button
like that before, though I've been using Excel for years. Well,
I did a Google search and found this excellent page:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=416

I converted the above code of our Dave's to a toggle button. It
now looks like so:

Option Explicit

Private Sub ToggleButton1_Click()

Dim iCol As Long

With Worksheets("2006 Realized Gains")
For iCol = 1 To 30
If .Cells(2, iCol).Font.ColorIndex = 5 _
And .Cells(2, iCol).Interior.ColorIndex = 24 Then

.Columns(iCol).Hidden = ToggleButton1.Value
Else
.Columns(iCol).Hidden = False
End If
Next iCol
End With
End Sub



Well, that works, and I think it's slick! (Setting the
.Hidden value to ToggleButton1.Value was my idea. If I
set the "Else" value to "Not ToggleButton1.Value", that's
kind of fun also: as you probably see already, the toggle
then is back and forth between hidden and unhidden columns.

I actually would find it useful (or at least loads of fun!)
to have a three-way toggle: (a) show the regular (normally
unhidden) cols; (b) show only the regularly hidden cols; and
(c) show all cols.

In order to do that, I suppose one way is to examine some
state or other at the top of the Else region. I'm stuck
for how, though. Ideas gladly entertained!

The next wish for improvement is to use something like Bob
Phillips shows in a concurrent article in this group,
Message-ID: :

Do While Activecell.Offset(0,-1).Value < ""
'do some stuff
Activecell.Offset(1,0).Select
Loop

I see what he's doing, but I am too weak on actual VBA
code to have yet been able to figure out how to incorporate
that concept into the above. In other words, lose the
"For 1-30" loop I have and replace it with a Do-While
loop.

Thanks for further suggestions!

Dallman Ross

--

Dave Peterson

In , Dave Peterson
spake thusly:

You'll want to change this line:
iLastCol = Cells.SpecialCells(xlCellTypeLastCell).Column
to
iLastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column

This extra dot means that excel will use the correct worksheet
(With Worksheets("2006 Realized Gains"))

Another way is if you can pick out a row that always has data:

With Worksheets("2006 Realized Gains")
iLastCol = .cells(1,.columns.count).end(xltoleft).column
...

I used row 1 to determine the last used column.

Thanks again, Dave!


Sometimes excel's lastusedcell won't be what you expect it to be.

I've occasionally found that out; yup! :-) (But so far, not with
this macro.)


Or visit Debra Dalgleish's site for some techniques for resetting that
lastusedcell.
http://www.contextures.com/xlfaqApp.html#Unused

Will do.

Dallman

The macro won't be the cause. But if you put something in the far right corner
(say IV65536 to be extreme), then delete the contents of that cell, you'll see
the problem.

Dallman Ross wrote:
<<snipped

Sometimes excel's lastusedcell won't be what you expect it to be.

I've occasionally found that out; yup! :-) (But so far, not with
this macro.)


Or visit Debra Dalgleish's site for some techniques for resetting that
lastusedcell.
http://www.contextures.com/xlfaqApp.html#Unused

Will do.

Dallman

--

Dave Peterson

In , Dave Peterson
spake thusly:

The macro won't be the cause. But if you put something in the
far right corner (say IV65536 to be extreme), then delete the
contents of that cell, you'll see the problem.

I believe that if you save the file after deletion of the
cell contents, the problem goes away.

In any case, I have a macro that looks for the bottom row.
In order to avoid those kinds of problems, I have the macro
save the file before it runs that particular statement.

==================================
Dallman Ross wrote: <<snipped

Sometimes excel's lastusedcell won't be what you expect it to
be.

I've occasionally found that out; yup! :-) (But so far, not
with this macro.)

Or visit Debra Dalgleish's site for some
techniques for resetting that lastusedcell.
http://www.contextures.com/xlfaqApp.html#Unused

Will do.

Dallman