Combo column identifier

[davegb]

I'm working on a set of worksheets in a workbook where I want to use a
macro to Hide certain columns before I protect them send them out to
end users. The starting column that I want to hide varies, depending on
how many columns of data there are in that particular sheet. On every
sheet, however, the columns I want to hide start with one that has the
text "Top 10" in the third row. The hidden columns end at Column AC in
all sheets.
My current thinking is to write a macro that selects row 3 and finds
the work "Top". Set the column as a variable "TopCol", then have XL
hide all columns from that column to Column AC. But I can't figure out
how to tell XL I want to select all columns from a variable, TopCol, to
a constant "AC". I know how to do ("AA:AC"), but what is the syntax
when one of the column identifiers is a variable, the other the normal
alpha label?

 

[Doug Glancy]

Dave,

This assumes you have the code to find your variable column:

Sub test()

Dim top_col As Range
Dim cols_to_hide As Range

Set top_col = Columns("AA") 'code to determine top_col will replace this

Set cols_to_hide = Range(top_col, Columns("AC"))
cols_to_hide.Hidden = True

End Sub

hth,

Doug

 

[davegb]

I tried running your code with mine, but there's a problem with my code
being based on finding a cell, not a range, and your code looking for a
range input. At this point, it looks like:

Sub Macro1()

Dim TopCol As Range
Dim Cols2Hide As Range

Rows("3:3").Select
Selection.Find(What:="top", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False).Activate
Set TopCol = ActiveCell.Column
Set Cols2Hide = Range(TopCol, Columns("AC"))
Cols2Hide.Hidden = True

End Sub

I'm getting a type mismatch at "Set TopCol=ActiveCell.column". I
understand why, just don't know how to fix it. Never do!

 

[Doug Glancy]

Dave,

This works for me:

Sub test()

Dim top_cell As Range
Dim TopCol As Range
Dim Cols2Hide As Range

Set top_cell = Rows(3).Find(What:="top", LookIn:=xlValues)
Set TopCol = Columns(top_cell.Column)
Set Cols2Hide = Range(TopCol, Columns("AC"))
Cols2Hide.Hidden = True

End Sub

Doug

 

[davegb]

Worked great, Doug. Thanks!

 

[Doug Glancy]

Dave,

Try this. It qualifies the ranges each time through with "wksht" otherwise
you would be referring to the first sheet each time. I also changed the
"wksht" declaration from "Object" to "Worksheet." Finally, I added a test
to make test whether it found "top" in row 3, if not it skips the next
steps.:

Sub AllSheetsColHide()
'for all sheets in currently active workbook, assigned to button

Dim TopCell As Range
Dim TopCol As Range
Dim Cols2Hide As Range
Dim wkSht As Worksheet

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues)
If Not TopCell Is Nothing Then ' if it found "top"
Set TopCol = .Columns(TopCell.Column)
Set Cols2Hide = .Range(TopCol, .Columns("AC"))
Cols2Hide.Hidden = True
End If
End With
Next wkSht

End Sub



hth,

Doug

 

[davegb]

Thanks, Doug, it works. But I'm even more confused, if that's possible!
I modified my code bases on yours trying to figure out what was wrong
with mine. I left out the if statement because right now it isn't
needed. But I still get the same error in the same place!

I ended up with:
Sub AllSheetsColHide()
'for all sheets in currently active workbook, assigned to button


Dim TopCell As Range
Dim TopCol As Range
Dim Cols2Hide As Range
Dim wkSht As Worksheet

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues)
Set TopCol = .Columns(TopCell.Column)
<----------------------[object variable or block variable not set]
Set Cols2Hide = .Range(TopCol, .Columns("AC"))
Cols2Hide.Hidden = True

End With

Next wkSht

End Sub

Why does this not run? It's exactly the same as yours without the If
statement.

Also, you wrote:
It qualifies the ranges each time through with "wksht" otherwise
you would be referring to the first sheet each time.

What does this mean? How do you "qualify a range"?

 

[Doug Glancy]

Dave,

You say the If statement is not needed now, but does it work with the If
statement in?

The way you've got it set up, the module loops through each worksheet in the
workbook and searches for the value "top" in the 3rd row. If it doesn't
find it in a given sheet then TopCell will be null and setting TopCol will
cause the error you're seeing. The If test I put in checks whether TopCell
has a value, i.e., the Find Function found "top" on Row3 of that sheet. If
it didn't then the If statement skips the rest of the code, thereby skipping
the error you're getting.

So again, does it work with the If statement. If not, does it error the
first time through the loop or on later sheets?

Doug

 

[davegb]

Doug, thanks for your reply.
It works with the IF statement, and without it, errors on the first
try.

 

[Doug Glancy]

Dave,

So, leave the If statement in and you should be good, since it won't do
anything on sheets without "top" in row 3.

If there's something I'm not understanding this let me know.

hth,

Doug

 

[davegb]

Thanks, Doug.