How to ignore hidden columns

[elmnas]

Hello people,

I have made following code,
this is a very easy loop that loops all used cells in active sheet.




Sub test()


For x = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
' put some code here.
Next x


End Sub

How can I make the loop ignore hidden columns?

Thank you in advance

[PAB]

Hi elmnas,

It depends on what you are going to do within the loop?
As a start, you could look at a If...End If loop directly inside the For...Next loop.
Perhaps something along the lines of if the column is hiiden then ignore that column and move onto the next.

I hope this gives you a start point.

[elmnas]

I gonna do a search and replace




Dim wks As Worksheet
Dim fndList As Variant
Dim rplcList As Variant

Dim x As Long



Set wks = ActiveWorkbook.ActiveSheet

fndList = Array("ä", "ö", "ü", "Ä", "Ö", "Ü", "ß")
rplcList = Array("ä", "ö", "ü", "Ä", "Ö", "Ü", "ß")
Application.ScreenUpdating = False

With wks


For x = LBound(fndList) To UBound(fndList)
ActiveSheet.Cells.Replace.Replace what:=fndList(x), replacement:=rplcList(x), lookat:=xlPart
Next x



Application.ScreenUpdating = True
Set wks = Nothing
Erase fndList
Erase rplcList

[Paul_Hossler]

Not tested, but maybe something like this




Option Explicit
Sub test()
Dim r As Range
Dim x As Long
Dim fndList(1 To 10) As String, rplcList(1 To 10) As String

'test data
ActiveSheet.Range("A1:Z26").Value = 123
ActiveSheet.Columns(5).Hidden = True
ActiveSheet.Columns(10).Hidden = True
ActiveSheet.Columns(15).Hidden = True

On Error Resume Next
With ActiveSheet
Set r = Intersect(.UsedRange, .UsedRange.SpecialCells(xlCellTypeVisible))
End With
On Error GoTo 0

If r Is Nothing Then Exit Sub

MsgBox r.Address

For x = LBound(fndList) To UBound(fndList)
r.Replace what:=fndList(x), replacement:=rplcList(x), lookat:=xlPart
Next x

End Sub