PDA

View Full Version : Search, find, hide columns - VBA



Koszykowa
07-18-2019, 11:26 AM
Hello,

I have an excel file: columns A:G contain not very important data, but H:XXX contains the translated data. In each column from H to the end I have different language and country (e.g. de_DE or el_GR). I would like to write a code that is looking for in header a local that I want to have unhide and the rest (from H to XXX) will be hide. The data A:G stay untouched.

I wrote something but it does not work:

Sub Hajd()



Dim locale As String
locale = InputBox("Which column should not be hidden?", "Hide columns")

For i = 8 To 1000

If Cells(1, i).Value = locale Then
Cells(1, i).Column.Visible = True
Else
Cells(1, i).Column.Visible = False
End If
Next


End Sub


Could you please help me?

Many thanks

p45cal
07-18-2019, 11:48 AM
There is no .visible property of a range, only .hidden.

Dim locale As String
locale = InputBox("Which column should not be hidden?", "Hide columns")
For i = 8 To 1000
If Cells(1, i).Value = locale Then
Columns(i).Hidden = False
Else
Columns(i).Hidden = True
End If
Next

but faster (and case insensitive (so you don't have to get the header capitals correct)):
Dim locale As String
locale = InputBox("Which column should not be hidden?", "Hide columns")
locale = UCase(locale)
Columns("H:ALL").Hidden = True
For i = 8 To 1000
If UCase(Cells(1, i).Value) = locale Then Columns(i).Hidden = False
Next

Koszykowa
07-18-2019, 12:05 PM
Thank you so much! That's very kind! :friends::friends::friends: