Hide Column Target Cell Clicked - From a List

[dj44]

Hi folks,

good tuesday :)

I am trying to hide some columns

I have made 2 lists in a worksheet.

When I click on Cell M1 it will hide the column AF etc

Click Cell | Hide Columns

M1 | AF
N8 | BX

Etc

I have also done this




Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

HideMyColumns Target

End Sub

Function HideMyColumns(oClickedCell As Range)

Dim i As Integer

For i = 2 To 8
While Range("A" & i).Value <> ""

If Not Intersect(oClickedCell, Range(Range("A" & i).Value)) Is Nothing Then

Columns.Range(Worksheet("ColumnList").Range("A" & i).Offset(0, 1).Value).EntireColumn.Hidden = True


Exit Function
End If
i = i + 1
Wend
Next
End Function


Unfortunately it requires something . But im not sure what it means, sub or function not defined?

Thank you for any help on this problem

[mana]

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Dim m

Set r = Worksheets("ColumnList").Range("A2:B8")

m = Application.VLookup(Target.Address(False, False), r, 2, False)
If IsError(m) Then Exit Sub
Columns(m).Hidden = True

End Sub

[dj44]

Hello Mana,

Thank you very much for a new version of the code.

As an added bonus I was able to hide the rows by just replacing 1 word

Well simplicity is best for us folks who like copy and paste :grinhalo:




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Dim m

Set r = Worksheets("RowsList").Range("A2:B8")

m = Application.VLookup(Target.Address(False, False), r, 2, False)
If IsError(m) Then Exit Sub
Rows(m).Hidden = True

End Sub


I have never seen this Vlookup with a target selection before, but I have put that on my reading list now

Thank you my friend and

Have a great day :beerchug: