Excel VBA to hide columns

[toveyj]

I am scratching my head trying to work out why this simple bit of vba doesn't work.
I have a simple loop to find the first column number in a range that isn't empty.
Then I hide all columns in the range apart from the last one that wasn't empty.

What I can't understand is that this works on one sheet. However, when I do
exactly the same thing on another (unprotected) sheet it doesn't. If I break down
the line that hides the columns in a range to a)select the range then b) set the
selected range to hidden then I get an error (application-defined or object-defined error).
Which suggests the range isn't vaild - but I can't see why. I expect I am doing something
basic wrong but I can't see what it is!


Sub Test()


ActiveWorkbook.Sheets("Locality Summary 17-18").Select

' find the column with the one that has the latest non-blank data in
For i = 16 To 27
ActiveSheet.Cells(22, i).Select

If ActiveCell.Value <> "" Then
Let Col_Num = i
End If
Next i

'set all month columns to hidden
Range(Cells(1, 16), Cells(Rows.Count, 27)).Select ' this works no problem
Selection.EntireColumn.Hidden = True
Range(Cells(1, 16), Cells(Rows.Count, 27)).EntireColumn.Hidden = True ' this works (quicker)
Range(Cells(1, 3), Cells(Rows.Count, 14)).EntireColumn.Hidden = True
Range(Cells(1, 29), Cells(Rows.Count, 40)).EntireColumn.Hidden = True

'unhide latest month
Range(Cells(1, Col_Num), Cells(Rows.Count, Col_Num)).EntireColumn.Hidden = False
Range(Cells(1, Col_Num - 13), Cells(Rows.Count, Col_Num - 13)).EntireColumn.Hidden = False
Range(Cells(1, Col_Num + 13), Cells(Rows.Count, Col_Num + 13)).EntireColumn.Hidden = False

Set Col_Num = Nothing

ActiveWorkbook.Sheets("Provider Summary 17-18").Select 'select another sheet in same format

For i = 94 To 105
ActiveSheet.Cells(22, i).Select

If ActiveCell.Value <> "" Then
Let Col_Num = i
End If
Next i

Range(Cells(1, 94), Cells(Rows.Count, 105)).Select ' this gives an object error?
Selection.EntireColumn.Hidden = True
'unhide latest month
Range(Cells(1, Col_Num), Cells(Rows.Count, Col_Num)).EntireColumn.Hidden = False


Set Col_Num = Nothing

End Sub

 

[PatOBrien198]

It looks like you are trying to hide columns CP thru DA (94 thru 105).

Try:

Range(Cells(1, 94), Cells(1, 105)).EntireColumn.Hidden = True

instead of:
Range(Cells(1, 94), Cells(Rows.Count, 105)).Select ' this gives an object error?
Selection.EntireColumn.Hidden = True

 

[toveyj]

Thanks this did in fact work. I'm not sure why Rows.Count worked on another sheet but not this one. However, as you say, it isn't really necessary if you just need to select a range which includes all the relevant columns and then hide them all.