Using VBA to hide columns doesn't hide them the same as "hide"

[Jimbomack]

Hi,
I have a worksheet that contains data in columns A through ZY. I have the worksheet window split between columns M and N. All columns from N through ZY are titled with dates. I want to use a macro to hide certain columns from N through to a user selected column that is based on the date title. The user enters a date in a cell and runs the Macro and all columns from N through to that dated column are then hidden. The reason for hiding these columns is so that when the user drags from a column to the left of N, over the hidden columns, the hidden columns remain hidden, unlike if the window was simply split at column M-N.
When I "hide" the columns using the traditional drop down in Excel, it works fine as desired. Those columns never show up, regardless of where I drag my mouse.

The Macro below works like a charm in hiding the columns, but when I drag across the boundary over the hidden columns, the hidden columns reappear. It's like the VBA code for hiding columns is not the same as the basic "Hide" command found in the drop down in Excel. I've read/proven that "Veryhidden" does not work for columns.
What am I doing wrong?
Thx

Sub hidecolumns()
Dim X As Variant
X = Range("j2").Value
Columns("N").Columns(X).Select
Selection.Columns.Hidden = True
End Sub

 

[JoeMo]

What's the value of J2 when you run this macro?

 

[Jimbomack]

it is a column address, such "AD" for example. So if J2 = AD, then the macro hides columns N through AD.

As mentioned, it hides the columns, but not the same as when using the "Hide" command from the drop down.

Thx

 

[VBE313]

Try to avoid using Select.

The hidden in VBA works the exact same as hiding it manually.

Sub hideColumn()
Dim X As String
With Sheets("Sheet1") 'Change this to your sheet name
    X = .Range("J2").Value
    .Columns("N").EntireColumn.Hidden = True
    .Columns(X).EntireColumn.Hidden = True
End With
End Sub

 

[Fluff]

How about

Sub hidecolumns()
Dim X As Variant
X = Range("j2").Value
Columns("N:" & X).Hidden = True
End Sub

 

[VBE313]

How about

Sub hidecolumns()
Dim X As Variant
X = Range("j2").Value
Columns("N:" & X).Hidden = True
End Sub

what if the column is before N? I tried that and it hid B:N but i think he would only want columns B and N hidden and the rest to be shown.

 

[Fluff]

The OP said

So if J2 = AD, then the macro hides columns N through AD.

Which is not the same as hide N & AD

 

[Jimbomack]

Hi,
This one worked perfectly.

Sub hidecolumns()
Dim X As Variant
X = Range("j2").Value
Columns("N:" & X).Hidden = True
End Sub

It hides the columns, and when I drag/select across the hidden columns, they stay hidden.
I'm new to VBA programming and could not figure out how to combine the variable into one line like you did: "Columns("N:" & X).Hidden = True". The difference seems to be the use of "selection", and that I used " Columns("N").Columns(X).Select" Something in this sequence does not hide the columns like the "Hide" command, but simply scrolls the columns I want hidden under the A-N set of columns, similar to a split window effect.

Super thankful to all of you.
Sincerely.
Any suggestions on how to best learn more VBA. I'm an old dog, using youtube videos like Learnit, WiseOwl ect. Love this stuff.
Cheers

 

[Fluff]

Using this Columns("N").Columns(X) does not hide columns N to whatever, it only hides one column, so if you have AD in J2 it will actually hide column AQ.

 

[Jimbomack]

Thanks for the explanation