unhiding columns problem
 

Thanks for the response but I am a novice at this and I can't figure out what
sh.columns(21) means. Could you please explain it and how I am to use it?

Thank you

Neal


"Tom Ogilvy" wrote in message
...
for each sh in Worksheets
sh.Columns(21).Hidden = False
Next

or

for each sh in Activewindow.SelectedSheets
sh.Columns(21).Hidden = False
Next


--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
I have a workbook with seven worksheets. I have recorded several macros
that
have selected all the worksheets and then cleared the contents in a range.
I
have used this same method to hide a column in all the worksheets
successfully
but when I try to unhide a column, it only works on one of the worksheets.
What
code can I use to to unhide the selected column on all seven worksheets.
Thanks
Neal

unhiding columns problem
 

sh is a reference to the sheet where you want a column unhidden.
Columns(21) refers to the 21st column.

? columns(1).address
$A:$A
? columns(21).address
$U:$U

So column U.

Just used 21 as an example since you didn't specify which column.

--
Regards,
Tom Ogilvy



"Neal" wrote in message
...
Thanks for the response but I am a novice at this and I can't figure out
what
sh.columns(21) means. Could you please explain it and how I am to use it?

Thank you

Neal


"Tom Ogilvy" wrote in message

for each sh in Worksheets
sh.Columns(21).Hidden = False
Next

or

for each sh in Activewindow.SelectedSheets
sh.Columns(21).Hidden = False
Next


--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
I have a workbook with seven worksheets. I have recorded several
macros
that
have selected all the worksheets and then cleared the contents in a
range.
I
have used this same method to hide a column in all the worksheets
successfully
but when I try to unhide a column, it only works on one of the
worksheets.
What
code can I use to to unhide the selected column on all seven
worksheets.
Thanks
Neal

unhiding columns problem
 

I suppose I did not make myself clear on what was needed. I will have to study
VBA more before I ask questions because this makes no sense to me. I have
figured out how to do it by recording a macro but I will have to add 72 macros
to the worksheet to accomplish it. I was hoping for an easier solution.

Neal
..
"Tom Ogilvy" wrote in message
...
sh is a reference to the sheet where you want a column unhidden.
Columns(21) refers to the 21st column.

? columns(1).address
$A:$A
? columns(21).address
$U:$U

So column U.

Just used 21 as an example since you didn't specify which column.

--
Regards,
Tom Ogilvy



"Neal" wrote in message
...
Thanks for the response but I am a novice at this and I can't figure out
what
sh.columns(21) means. Could you please explain it and how I am to use it?

Thank you

Neal


"Tom Ogilvy" wrote in message

for each sh in Worksheets
sh.Columns(21).Hidden = False
Next

or

for each sh in Activewindow.SelectedSheets
sh.Columns(21).Hidden = False
Next


--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
I have a workbook with seven worksheets. I have recorded several
macros
that
have selected all the worksheets and then cleared the contents in a
range.
I
have used this same method to hide a column in all the worksheets
successfully
but when I try to unhide a column, it only works on one of the
worksheets.
What
code can I use to to unhide the selected column on all seven
worksheets.
Thanks
Neal

unhiding columns problem
 

What is it you want to do. Perhaps you just need to state what you want to
do specifically in plain english.

Regards,
Tom Ogilvy


Neal wrote in message
...
I suppose I did not make myself clear on what was needed. I will have to
study
VBA more before I ask questions because this makes no sense to me. I
have
figured out how to do it by recording a macro but I will have to add 72
macros
to the worksheet to accomplish it. I was hoping for an easier solution.

Neal
.
"Tom Ogilvy" wrote in message
...
sh is a reference to the sheet where you want a column unhidden.
Columns(21) refers to the 21st column.

? columns(1).address
$A:$A
? columns(21).address
$U:$U

So column U.

Just used 21 as an example since you didn't specify which column.

--
Regards,
Tom Ogilvy



"Neal" wrote in message
...
Thanks for the response but I am a novice at this and I can't figure
out
what
sh.columns(21) means. Could you please explain it and how I am to use
it?

Thank you

Neal


"Tom Ogilvy" wrote in message

for each sh in Worksheets
sh.Columns(21).Hidden = False
Next

or

for each sh in Activewindow.SelectedSheets
sh.Columns(21).Hidden = False
Next


--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
I have a workbook with seven worksheets. I have recorded several
macros
that
have selected all the worksheets and then cleared the contents in
a
range.
I
have used this same method to hide a column in all the worksheets
successfully
but when I try to unhide a column, it only works on one of the
worksheets.
What
code can I use to to unhide the selected column on all seven
worksheets.
Thanks
Neal

unhiding columns problem
 

I think you are absolutely right, I need to restate what I need. Sorry about my
inexperience with VBA.

I have a workbook that tracks employee hours in different labor catagories on a
daily basis. There are 37 labor catagories and therefore 37 columns and 7
worksheets (Mon-Sun). On some job projects, all of the 37 labor catagories are
not needed. Perhaps 3 or 4 could be eliminated. Those that can be eliminated
are not grouped together. They could be any of the 37. I need a macro that will
allow me to hide the labor catagory columns that I don't need on all 7 days and
also unhide them when needed. Ideally I would want to list the labor catagories
on another worksheet and perhaps put a X in the column next to the catagory that
I would like hidden and then run the macro and it would hide the columns with a
X. I have tried this by adding a If Then argument to a recorded macro but it
only hides the columns and does not unhide them. Also, I would have to have a
macro for each catagory using this method. Is there any easier way? Can
someone point me in the right direction?

Your help is appreciated
Neal


"Tom Ogilvy" wrote in message
...
What is it you want to do. Perhaps you just need to state what you want to
do specifically in plain english.

Regards,
Tom Ogilvy


Neal wrote in message
...
I suppose I did not make myself clear on what was needed. I will have to
study
VBA more before I ask questions because this makes no sense to me. I
have
figured out how to do it by recording a macro but I will have to add 72
macros
to the worksheet to accomplish it. I was hoping for an easier solution.

Neal
.
"Tom Ogilvy" wrote in message
...
sh is a reference to the sheet where you want a column unhidden.
Columns(21) refers to the 21st column.

? columns(1).address
$A:$A
? columns(21).address
$U:$U

So column U.

Just used 21 as an example since you didn't specify which column.

--
Regards,
Tom Ogilvy



"Neal" wrote in message
...
Thanks for the response but I am a novice at this and I can't figure
out
what
sh.columns(21) means. Could you please explain it and how I am to use
it?

Thank you

Neal


"Tom Ogilvy" wrote in message

for each sh in Worksheets
sh.Columns(21).Hidden = False
Next

or

for each sh in Activewindow.SelectedSheets
sh.Columns(21).Hidden = False
Next


--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
I have a workbook with seven worksheets. I have recorded several
macros
that
have selected all the worksheets and then cleared the contents in
a
range.
I
have used this same method to hide a column in all the worksheets
successfully
but when I try to unhide a column, it only works on one of the
worksheets.
What
code can I use to to unhide the selected column on all seven
worksheets.
Thanks
Neal

unhiding columns problem
 

Tom,

I was just adding some explanation to the code.
Neal appears to be a novice and seems to need more detail on what is
happening.

steve

"Tom Ogilvy" wrote in message
...
Except for enclosing the code in the name of a function and declaring a
variable, I don't see any added functionality.

You stated:

works fairly well if you want to do the same column on all the
worksheets.

Your macro should look more like this

as if you were going to show something more robust and exciting?

--
Regards,
Tom Ogilvy



steve wrote in message
...
Neal,

Don't give up yet.

The code given you
for each sh in Activewindow.SelectedSheets
sh.Columns(21).Hidden = False
Next
works fairly well if you want to do the same column on all the
worksheets.

Your macro should look more like this
lines with ' are my comments

Sub UnhideMyColumn()
' you can choose your own name for this

Dim sh As Worksheet
' tells Excel that sh is a worksheet object

For each sh in Activewindow.SelectedSheets
' cycles from the first worksheet to the last

sh.Columns(21).Hidden = False
' unhides column 21

Next
' goes to next sheet

End Sub

You can use this same code to hide a column by changing False to True.

steve

"Neal" wrote in message
...
I suppose I did not make myself clear on what was needed. I will have
to
study
VBA more before I ask questions because this makes no sense to me. I
have
figured out how to do it by recording a macro but I will have to add
72
macros
to the worksheet to accomplish it. I was hoping for an easier
solution.

Neal
.
"Tom Ogilvy" wrote in message
...
sh is a reference to the sheet where you want a column unhidden.
Columns(21) refers to the 21st column.

? columns(1).address
$A:$A
? columns(21).address
$U:$U

So column U.

Just used 21 as an example since you didn't specify which column.

--
Regards,
Tom Ogilvy



"Neal" wrote in message
...
Thanks for the response but I am a novice at this and I can't
figure
out
what
sh.columns(21) means. Could you please explain it and how I am to
use
it?

Thank you

Neal


"Tom Ogilvy" wrote in message

for each sh in Worksheets
sh.Columns(21).Hidden = False
Next

or

for each sh in Activewindow.SelectedSheets
sh.Columns(21).Hidden = False
Next


--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
I have a workbook with seven worksheets. I have recorded
several
macros
that
have selected all the worksheets and then cleared the contents
in
a
range.
I
have used this same method to hide a column in all the
worksheets
successfully
but when I try to unhide a column, it only works on one of the
worksheets.
What
code can I use to to unhide the selected column on all seven
worksheets.
Thanks
Neal

unhiding columns problem
 

Assume you have a sheet with 37 cells in column B containing an X if you
want that column hidden. If I want column B hidden, I put an X in B2, if I
want column AA hidden, I put an X in B27.

You can have the 37 labels in column A, but that is not needed by the macro.
Assume this sheet is named data and the only other sheets in the workbook
are the 7 sheets.

Sub HandleColumns()
Dim varr()
Dim sStr As String, rng As Range
Dim sh As Worksheet
On Error Resume Next
With Worksheets("Data")
Set rng = .Range(.Cells(1, 2), .Cells(37, 2)). _
SpecialCells(xlConstants, xlTextValues)
End With
On Error GoTo 0
if rng is nothing then exit sub
ReDim varr(1 To rng.Count)
sStr = ""
For Each cell In rng
sCol = Left(Cells(1, cell.Row).Address(0, 0), 2 + (cell.Row < 27))
sStr = sStr & sCol & ":" & sCol & ","
Next
sStr = Left(sStr, Len(sStr) - 1)
If Len(Trim(sStr)) = 0 Then Exit Sub
For Each sh In Worksheets
If LCase(sh.Name) < "data" Then
sh.Columns.Hidden = False
sh.Range(sStr).EntireColumn.Hidden = True
End If
Next

End Sub

--
Regards,
Tom Ogilvy


"Neal" wrote in message
...
I think you are absolutely right, I need to restate what I need. Sorry
about my
inexperience with VBA.

I have a workbook that tracks employee hours in different labor catagories
on a
daily basis. There are 37 labor catagories and therefore 37 columns and 7
worksheets (Mon-Sun). On some job projects, all of the 37 labor
catagories are
not needed. Perhaps 3 or 4 could be eliminated. Those that can be
eliminated
are not grouped together. They could be any of the 37. I need a macro
that will
allow me to hide the labor catagory columns that I don't need on all 7
days and
also unhide them when needed. Ideally I would want to list the labor
catagories
on another worksheet and perhaps put a X in the column next to the
catagory that
I would like hidden and then run the macro and it would hide the columns
with a
X. I have tried this by adding a If Then argument to a recorded macro but
it
only hides the columns and does not unhide them. Also, I would have to
have a
macro for each catagory using this method. Is there any easier way? Can
someone point me in the right direction?

Your help is appreciated
Neal


"Tom Ogilvy" wrote in message
...
What is it you want to do. Perhaps you just need to state what you want
to
do specifically in plain english.

Regards,
Tom Ogilvy


Neal wrote in message
...
I suppose I did not make myself clear on what was needed. I will have
to
study
VBA more before I ask questions because this makes no sense to me. I
have
figured out how to do it by recording a macro but I will have to add
72
macros
to the worksheet to accomplish it. I was hoping for an easier
solution.

Neal
.
"Tom Ogilvy" wrote in message
...
sh is a reference to the sheet where you want a column unhidden.
Columns(21) refers to the 21st column.

? columns(1).address
$A:$A
? columns(21).address
$U:$U

So column U.

Just used 21 as an example since you didn't specify which column.

--
Regards,
Tom Ogilvy



"Neal" wrote in message
...
Thanks for the response but I am a novice at this and I can't
figure
out
what
sh.columns(21) means. Could you please explain it and how I am to
use
it?

Thank you

Neal


"Tom Ogilvy" wrote in message

for each sh in Worksheets
sh.Columns(21).Hidden = False
Next

or

for each sh in Activewindow.SelectedSheets
sh.Columns(21).Hidden = False
Next


--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
I have a workbook with seven worksheets. I have recorded
several
macros
that
have selected all the worksheets and then cleared the contents
in
a
range.
I
have used this same method to hide a column in all the
worksheets
successfully
but when I try to unhide a column, it only works on one of the
worksheets.
What
code can I use to to unhide the selected column on all seven
worksheets.
Thanks
Neal

unhiding columns problem
 

I didn't give you away to show all columns by not having any X's in column
B. Here is an addition.

Sub HandleColumns()
Dim sStr As String, rng As Range
Dim sh As Worksheet
On Error Resume Next
With Worksheets("Data")
Set rng = .Range(.Cells(1, 2), .Cells(37, 2)). _
SpecialCells(xlConstants, xlTextValues)
End With
On Error GoTo 0
if rng is nothing then
For Each sh In Worksheets
If LCase(sh.Name) < "data" Then
sh.Columns.Hidden = False
End If
Next
exit sub
End if
sStr = ""
For Each cell In rng
sCol = Left(Cells(1, cell.Row).Address(0, 0), 2 + (cell.Row < 27))
sStr = sStr & sCol & ":" & sCol & ","
Next
sStr = Left(sStr, Len(sStr) - 1)
If Len(Trim(sStr)) = 0 Then Exit Sub
For Each sh In Worksheets
If LCase(sh.Name) < "data" Then
sh.Columns.Hidden = False
sh.Range(sStr).EntireColumn.Hidden = True
End If
Next

End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Assume you have a sheet with 37 cells in column B containing an X if you
want that column hidden. If I want column B hidden, I put an X in B2, if
I
want column AA hidden, I put an X in B27.

You can have the 37 labels in column A, but that is not needed by the
macro.
Assume this sheet is named data and the only other sheets in the workbook
are the 7 sheets.

Sub HandleColumns()
Dim varr()
Dim sStr As String, rng As Range
Dim sh As Worksheet
On Error Resume Next
With Worksheets("Data")
Set rng = .Range(.Cells(1, 2), .Cells(37, 2)). _
SpecialCells(xlConstants, xlTextValues)
End With
On Error GoTo 0
if rng is nothing then exit sub
ReDim varr(1 To rng.Count)
sStr = ""
For Each cell In rng
sCol = Left(Cells(1, cell.Row).Address(0, 0), 2 + (cell.Row < 27))
sStr = sStr & sCol & ":" & sCol & ","
Next
sStr = Left(sStr, Len(sStr) - 1)
If Len(Trim(sStr)) = 0 Then Exit Sub
For Each sh In Worksheets
If LCase(sh.Name) < "data" Then
sh.Columns.Hidden = False
sh.Range(sStr).EntireColumn.Hidden = True
End If
Next

End Sub

--
Regards,
Tom Ogilvy


"Neal" wrote in message
...
I think you are absolutely right, I need to restate what I need. Sorry
about my
inexperience with VBA.

I have a workbook that tracks employee hours in different labor
catagories
on a
daily basis. There are 37 labor catagories and therefore 37 columns and
7
worksheets (Mon-Sun). On some job projects, all of the 37 labor
catagories are
not needed. Perhaps 3 or 4 could be eliminated. Those that can be
eliminated
are not grouped together. They could be any of the 37. I need a macro
that will
allow me to hide the labor catagory columns that I don't need on all 7
days and
also unhide them when needed. Ideally I would want to list the labor
catagories
on another worksheet and perhaps put a X in the column next to the
catagory that
I would like hidden and then run the macro and it would hide the columns
with a
X. I have tried this by adding a If Then argument to a recorded macro
but
it
only hides the columns and does not unhide them. Also, I would have to
have a
macro for each catagory using this method. Is there any easier way?
Can
someone point me in the right direction?

Your help is appreciated
Neal


"Tom Ogilvy" wrote in message
...
What is it you want to do. Perhaps you just need to state what you
want
to
do specifically in plain english.

Regards,
Tom Ogilvy


Neal wrote in message
...
I suppose I did not make myself clear on what was needed. I will
have
to
study
VBA more before I ask questions because this makes no sense to me.
I
have
figured out how to do it by recording a macro but I will have to add
72
macros
to the worksheet to accomplish it. I was hoping for an easier
solution.

Neal
.
"Tom Ogilvy" wrote in message
...
sh is a reference to the sheet where you want a column unhidden.
Columns(21) refers to the 21st column.

? columns(1).address
$A:$A
? columns(21).address
$U:$U

So column U.

Just used 21 as an example since you didn't specify which column.

--
Regards,
Tom Ogilvy



"Neal" wrote in message
...
Thanks for the response but I am a novice at this and I can't
figure
out
what
sh.columns(21) means. Could you please explain it and how I am
to
use
it?

Thank you

Neal


"Tom Ogilvy" wrote in message

for each sh in Worksheets
sh.Columns(21).Hidden = False
Next

or

for each sh in Activewindow.SelectedSheets
sh.Columns(21).Hidden = False
Next


--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
I have a workbook with seven worksheets. I have recorded
several
macros
that
have selected all the worksheets and then cleared the
contents
in
a
range.
I
have used this same method to hide a column in all the
worksheets
successfully
but when I try to unhide a column, it only works on one of
the
worksheets.
What
code can I use to to unhide the selected column on all seven
worksheets.
Thanks
Neal