CODEWITHSUNDEEP
vbaexcel
Milo Persic
Milo Persic

Reputation: 1118

How do I select multiple column ranges using "If Columns" in VBA?

I'm using this code on a toggle button to hide/show columns in my spreadsheet. 

Sub Button22_Click()
If Columns("D:E").Hidden = True Then
Columns("D:E").Hidden = False
Else
Columns("D:E").Hidden = True
End If
End Sub

This formula works great for columns D:E. The question is, how do I add other columns, both single and ranges, to this formula? Suppose I need to show/hide F:K and N as well, with the same button? I've tried things like ("D:E","F:K") and I get a runtime error. Any help is VERY appreciated.

Upvotes: 1

Views: 5479

Answers (2)

GELR
GELR

Reputation: 1291

You have to use the Range object.

Range("D:E,F:K,N:N").Select
Selection.Hidden = True

https://msdn.microsoft.com/en-us/library/office/ff838238.aspx

Update:

Your code should look like this:

Sub Button22_Click()
    Range("D:E,F:K,N:N").Select
    Selection.EntireColumn.Hidden = Not Selection.EntireColumn.Hidden
End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96791

Just syntax to hide a disjoint set of columns:

Sub dural()
   Range("D:E,L:L").EntireColumn.Hidden = True
End Sub

This will hide columns D, E, and L.

Upvotes: 1

Related Questions