VBA to hide columns (XP)

Topic

New Reply

Hi Loungers,

I’m having trouble with the syntax needed to hide a range of columns. If the columns were known, I’d use something per the macro recorder like
Columns(“B:C”).Select
Range(“B7”).Activate
Selection.EntireColumn.Hidden = True

But the columns are not known (they can change whenever I run the macro). So the macro prompts for the set of columns such as
first_col_hide = inputbox(“enter first col”,,”xxx”)
last_col_hide = inputbox(“enter last col”,,”xxx”)

Using the 2 variables above (which are dim’d as strings) per the syntax from the macro recorder does not work. I must be having a brain fog bcs, after trying several combinations, I can’t still figure it out. I’m wondering if I need to use an offset in this case ???

TIA

Fred

Reply | Quote

Replies

Edited by HansV to correct error

You can use

Range(first_col_hide & “:” & last_col_hide).EntireColumn.Hidden = True

Note that this line doesn’t select the range to be hidden.

Reply | Quote

Hi Hans,

Thanks much. I couldn’t think of concatenating the columns with a : No problem with the columns to be hidden not be selected – not sure why I’d want to do that anyway.

However, my system did not support a method of …EntireColumn.Hide. Not sure if this is in 2007 (if you have that yet). I had to do …EntireColumn.Hidden = True.

Fred

Reply | Quote

Sorry, the .Hide was a stupid mistake, it should indeed be .Hidden = True. I have corrected my previous reply.

Reply | Quote

There are a couple of other ways to do this.
These use the Application.InputBox method rather than the InputBox function. One difference between the two is that Application.InputBox can accept ranges input via mouse.

Sub HideColumnsChosenWithMouse()
Dim uiFirstColumn As Range
Dim uiLastColumn As Range

On Error Resume Next
Set uiFirstColumn = Application.InputBox("Select the first column with the mouse", Type:=8)
On Error GoTo 0
If uiFirstColumn Is Nothing Then Exit Sub: Rem cancel pressed

On Error Resume Next
Set uiLastColumn = Application.InputBox("Select the last column with the mouse", Type:=8)
On Error GoTo 0
If uiLastColumn Is Nothing Then Exit Sub: Rem cancel pressed

Range(uiFirstColumn, uiLastColumn).EntireColumn.Hidden = True
End Sub

The user typing in a column address requires validation, in case they type an invalid column name, like “Apple Pie”

Sub HideColumnsFromTypedAddresses()
Dim uiFirstCollAddress As Variant
Dim uiLastCollAddress As Variant
Dim EntryIsValidColumnAddress As Boolean

Do
uiFirstCollAddress = Application.InputBox("Enter the number/letter of the first hidden column.", Type:=7)
If uiFirstCollAddress = False Then MsgBox "x": Exit Sub: Rem cancel pressed
On Error Resume Next
EntryIsValidColumnAddress = (TypeName(Columns(uiFirstCollAddress)) = "Range")
On Error GoTo 0
Loop Until EntryIsValidColumnAddress

EntryIsValidColumnAddress = False

Do
uiLastCollAddress = Application.InputBox("Enter the number/letter of the last hidden column.", Type:=7)
If uiLastCollAddress = False Then MsgBox "x": Exit Sub: Rem cancel pressed
On Error Resume Next
EntryIsValidColumnAddress = (TypeName(Columns(uiLastCollAddress)) = "Range")
On Error GoTo 0
Loop Until EntryIsValidColumnAddress

Range(Columns(uiFirstCollAddress), Columns(uiLastCollAddress)).Hidden = True
End Sub

Or you could have the user select the whole range of columns to be hidden rather than specify start and end.

Sub HideColumnsInOneSwellFoop()
Dim uiRangeSelected

On Error Resume Next
Set uiRangeSelected = Application.InputBox("Select cells from all the columns you want hidden.", Type:=8)
On Error GoTo 0
If uiRangeSelected Is Nothing Then Exit Sub: Rem Cancel pressed

uiRangeSelected.EntireColumn.Hidden = True
End Sub

Reply | Quote

Mike,

This looks good. I’ll have to give it a try when I have more time. I only resorted to inputbox since I didn’t know this method. Also, since this is just for me to save time, I’m hoping I can get the col letters correct. But I did think of that problem also and decided to forego the error checking.

Fred

Reply | Quote


With ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)
If .Areas.Count = 1 Then
MsgBox "no columns are hidden"
Else
MsgBox .Areas(2).Column & " is the first visible column after the hidden columns."
End If
End With

Reply | Quote

No problem, Hans.

One more question with hidden columns.

What I’m doing is setting up a print area to contain col A, which has a list of names, and then a set of columns for the current “period” (the period might be 2 weeks) – each day in the period has its own column.

So as the days go into the past, I want to hide those columns so that col A and the columns for the upcoming period appear adjacent. (I know that I can set up a Print Area for non-consecutive cols but then each group of adjacent cols will print on its own page.)

I know that I could use the inputbox to get both the first col and last col of the current period. But the first col will always be the col after the last col hidden unless this is the first time hiding columns (that is, the very first current period will start in col B and the previous col will not be hidden, since that’s col A with the names). So it seems like a waste to do the prompting. Therefore what I’d like is a way to determine the first non-hidden col, allowing for that first-time usage also, and just prompt for the last col to include in the print area (since the number of cols in the “current period” might change.

Thanks.

Fred

Reply | Quote

Try this:

Dim lngStart As Long
Dim lngEnd As Long
‘ Find last hidden column, stop at column B
For lngStart = Columns.Count To 2 Step -1
If Cells(1, lngStart).EntireColumn.Hidden = True Then Exit For
Next lngStart
‘ One to the right
lngStart = lngStart + 1
‘ Get last column to hide
lngEnd = Application.InputBox(Prompt:=”Select last column to be hidden”, Type:=8).Column
‘ Hide range of columns
Range(Cells(1, lngStart), Cells(1, lngEnd)).EntireColumn.Hidden = True

You can add error handling as in Mike’s examples.

Reply | Quote