How to exclude hidden rows/columns while executing a VB code

[jameshbabu]

Hi all,

i would like to know how to exclude the hidden rows/ columns while executing the VB code,

thanks

[marreco]

Try..


Sub Macro1()
If Rows("1:10").EntireRow.Hidden = True Then
Rows("1:10").EntireRow.Hidden = False
ElseIf Columns("2:3").EntireColumn.Hidden = False Then
Columns("2:3").EntireColumn.Hidden = True
End If

End Sub

[Bob Phillips]

Check the hidden property



For i = 2 To 20
If Not Rows(i).Hidden
'do stuff
End If
End With

[shrivallabha]

Or you can use specialcells method:

Dim rWork As Range
Set rWork = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)

[Paul_Hossler]

Be careful if there are MergedCells.

My experience is that merged cells will lead to unexpected (and oft times, unwanted) results when dealing with hidden rows and columns

Paul