Macro to list the hidden columns

[raghav17]

Hi Guys,

I am pretty new to VBA but I cam across a macro which lists out the hidden rows in a worksheet :-

Sub ShowRows()
Dim rng As Range
Dim r As Range
Dim sTemp As String

Set rng = Range("A1:A1000")
sTemp = ""
For Each r In rng.Rows
If r.EntireRow.Hidden Then
sTemp = sTemp & "Row " & Mid(r.Address, 4) & vbCrLf
End If
Next r

If sTemp > "" Then
sTemp = "The following rows are hidden:" & vbCrLf & _
vbCrLf & sTemp
MsgBox sTemp
Else
MsgBox "There are no hidden rows"
End If
End Sub


But now I want a macro which lists out the hidden column in same way. Thanks for help in advance.

 

[Michael M]

Maybe this

Sub ShowRows()
Dim i As Variant
Dim sTemp As String
sTemp = ""
LastCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
          SearchDirection:=xlPrevious).Column
For i = 1 To LastCol
If Columns(i).EntireColumn.Hidden Then
sTemp = sTemp & "Column " & (i) & vbCrLf
End If
Next i

If sTemp > "" Then
sTemp = "The following Columns are hidden:" & vbCrLf & _
vbCrLf & sTemp
MsgBox sTemp
Else
MsgBox "There are no hidden Columns"
End If
End Sub

 

[raghav17]

Thank you but I am getting run time error 91.

 

[Michael M]

You will get that error if you have a blank sheet ??
Is the code pasted into "This Workbook" module ?
IS the sheet to be checked the active sheet ??

 

[raghav17]

I am using "This Workbook". but still unable to run the macro. You can run the Macro I provided in my very first Post and I want a list similar to that. Thanks a lot.

 

[Michael M]

I've run the macro I posted in Post #2, with no issues ??
Have you stepped through manually to see where the error occurs ??

 

[raghav17]

LastCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

I am getting error in this command. You are running the macro on "Active sheet" or "This workbook"

 

[Michael M]

Can't see any reason for it not to work....but try..
AND
the code MUST be in either ThisWorkbook OR a Standard Module NOT the sheet module!!

Sub Showcolumns()
Dim i As Variant, sTemp As String
sTemp = ""
For i = 1 To ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
          SearchDirection:=xlPrevious).Column '16384
If Columns(i).EntireColumn.Hidden Then
sTemp = sTemp & "Column " & (i) & vbCrLf
End If
Next i
If sTemp > "" Then
sTemp = "The following Columns are hidden:" & vbCrLf & _
vbCrLf & sTemp
MsgBox sTemp
Else
MsgBox "There are no hidden Columns"
End If
End Sub

 

[raghav17]

Thnks Buddy, Its working now, the error was resolved when I changed the number of columns.

 

[Michael M]

Ummm, what di you mean by this...

the error was resolved when I changed the number of columns.

My code allowed for the last used column ???