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?

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" wrote in message
oups.com...
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?

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!

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" wrote in message
oups.com...
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!

Worked great, Doug. Thanks!

I've been modifying the code Doug gave me to hide the columns in every
worksheet in the workbook. But I'm getting an error, "Object variable
or with block variable not set" when I run the following:

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 Object

For Each wkSht In ActiveWorkbook.Worksheets

Set TopCell = Rows(3).Find(What:="top", LookIn:=xlValues)
Set TopCol = Columns(TopCell.Column)
<------------------------[error here]
Set Cols2Hide = Range(TopCol, Columns("AC"))
Cols2Hide.Hidden = True

Next wkSht

End Sub

Any suggestions? Thanks in advance!