AW: Spalten ausblenden
16.03.2018 15:32:00
Tino
Hallo,
geht es so, Makros entsprechend den Button zuweisen.
Bei der Suche sind Platzhalterzeichen zulässig.
https://docs.microsoft.com/de-de/dotnet/visual-basic/language-reference/operators/like-operator
Sub Vor()
Dim rng As Range
Dim n&, nn&, NextCol&
Set rng = ActiveSheet.UsedRange
Set rng = rng.Columns(2).Resize(, rng.Columns.Count - 1)
With rng.EntireColumn
For n = 1 To .Columns.Count
If .Columns(n).Hidden = False Then
nn = n + 1
For NextCol = nn To .Columns.Count
If .Cells(1, NextCol) "" Then
Exit For
End If
Next NextCol
Exit For
End If
Next n
If NextCol = 0 Or NextCol > .Columns.Count Then NextCol = 1
Application.ScreenUpdating = False
.Hidden = True
.Columns(NextCol).Hidden = False
Application.ScreenUpdating = True
End With
End Sub
Sub Zurueck()
Dim rng As Range
Dim n&, nn&, NextCol&
Set rng = ActiveSheet.UsedRange
Set rng = rng.Columns(2).Resize(, rng.Columns.Count - 1)
With rng.EntireColumn
For n = .Columns.Count To 1 Step -1
If .Columns(n).Hidden = False Then
nn = n - 1
For NextCol = nn To 1 Step -1
If .Cells(1, NextCol) "" Then
Exit For
End If
Next NextCol
Exit For
End If
Next n
If NextCol .Columns.Count Then
MsgBox "nix gefunden!"
Else
Application.ScreenUpdating = False
.Hidden = True
.Columns(n).Hidden = False
Application.ScreenUpdating = True
End If
End With
End Sub
Gruß Tino