Need a macro to hide certain columns

[Dallman Ross]

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

Long version, which includes my reasons:
I have a sheet with lots of complex formulas and found it has
gotten unwieldy and recalculates all the time now and takes way
too long to do so. I have now revised a lot of the formulas
and added various helper columns to reduce the calculations
per cell and, hopefully, get out of my calculation pickle.
(Haven't quite succeed yet in the latter, though, unfortunately.)
I hide all the helper columns. However, to revise the sheet
I'm unhiding all the hidden columns temporarily. I've formatted
these normally-hidden columns all a certain color and also
changed the font color there, so I can easily distinguish these
as columns to hide again. Now I want a macro to do that for me.

Would someone be kind enough to help me with such a macro?

Here's some pseudo-code:

For nextcol in columns 1-30
If {data range} = Hex Val. #CCCCFF
If {data range} font color = #0000FF
Hide column
End If
End If
End of For-Loop


Dankeschoen!

 

[Dave Peterson]

Maybe you can modify this macro to do what you want:

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

=====
Another approach would be to insert another row and put some kind of indicator
in it (x or leave it blank). Then just loop through the columns in that row.

 

[Dallman Ross]

Maybe you can modify this macro to do what you want:

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

That's excellent! I only had to modify it slightly.
It works perfectly! Thank you very much.

The color indexes weren't right, and I had to record a
dummy macro to see what the right ones were. (5 and 24.)
Also, my formatting starts on Row 2. So I changed the "1" cell
refs to "2". Very slick, Dave!

I saw another article here today with someone (you?)
showing how to run through the columns until a blank
is reached. Maybe I'll incorporate that in this instead
of just using my arbitrary "30" column-figure.

One question: what is the "Option Explicit" part for?


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

Short version: I want a macro to hide columns of a certain
cell color and font color.
[snip of "long version"]
Would someone be kind enough to help me with such a macro?

Here's some pseudo-code:

For nextcol in columns 1-30
If {data range} = Hex Val. #CCCCFF
If {data range} font color = #0000FF
Hide column
End If
End If
End of For-Loop

Dankeschoen!

 

[Dave Peterson]

Option Explicit forces you to declare your variables. So if excel finds
something that looks like a variable and it's not declared, your code won't
compile and you'll be shown an error.

It may seem like more work, but if you've ever spent time trying to determine
why:

ctr1 = ctrll + 1
didn't work the way you hoped, you'll see the savings.
(one of those is ctr-one and the other is ctr-ELL).

Maybe you can modify this macro to do what you want:

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

That's excellent! I only had to modify it slightly.
It works perfectly! Thank you very much.

The color indexes weren't right, and I had to record a
dummy macro to see what the right ones were. (5 and 24.)
Also, my formatting starts on Row 2. So I changed the "1" cell
refs to "2". Very slick, Dave!

I saw another article here today with someone (you?)
showing how to run through the columns until a blank
is reached. Maybe I'll incorporate that in this instead
of just using my arbitrary "30" column-figure.

One question: what is the "Option Explicit" part for?

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

Short version: I want a macro to hide columns of a certain
cell color and font color.
[snip of "long version"]
Would someone be kind enough to help me with such a macro?

Here's some pseudo-code:

For nextcol in columns 1-30
If {data range} = Hex Val. #CCCCFF
If {data range} font color = #0000FF
Hide column
End If
End If
End of For-Loop

Dankeschoen!

 

[Dallman Ross]

Option Explicit forces you to declare your variables. So if
excel finds something that looks like a variable and it's not
declared, your code won't compile and you'll be shown an error.

Ah. Very good, indeed. Thanks again!

dman (still haven't gotten rid of the slow-calculation blues on that sheet)

 

[Dallman Ross]

In response to my original --

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: <[email protected]>:

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]

Why not add 3 optionbuttons and then let the user select the one they want?

 

[Dallman Ross]

Why not add 3 optionbuttons and then let the user select the one
they want?

Well, the user is me. I don't want that many buttons devoted to
this one silly set of actions. I have other buttons in mind and
will have a whole damn row of buttons.


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

 

[Dallman Ross]

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, here:
http://www.parry.co.nz/findlastroworcolumn.html#Last Column in a Sheet

Dallman

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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:

 

[Dallman Ross]

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.

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