Excel string variables not working in range

Question

I'd really appreciate it if someone could point me to the right direction. I'm trying to do a simple macro to hide selected set of rows. I could combine Set 1 and 2 in the variables, but they are two different sets of information and I want them separated so it is easier to maintain as the code grows.

I'm wondering if there are ways to see how range resolves my variables.

This code works

Const Set1 As String = "1:2,4:5"
Const Set2 As String = "7:8,11:12"

Sub test()

Worksheets("Sheet1").Range(Set1).EntireRow.Hidden = True
Worksheets("Sheet1").Range(Set2).EntireRow.Hidden = True

End Sub

Sub test2()
Worksheets("Sheet1").Range("1:2,4:5,7:8,11:12").EntireRow.Hidden = True
End Sub

But not this

Const Set1 As String = "1:2,4:5"
Const Set2 As String = "7:8,11:12"


Sub test()

Worksheets("Sheet1").Range(Set1, Set2).EntireRow.Hidden = True

End Sub

What's wrong with the first test approach? The last one doesn't work because passing two arguments to Range() implies they are the top-left and bottom-right corners of a larger range. — Tim Williams
– Tim Williams, Commented Aug 15, 2020 at 0:38
Worksheets("Sheet1").Range(Set1 & "," & Set2).EntireRow.Hidden = True — Shivang Gupta
– Shivang Gupta, Commented Aug 15, 2020 at 0:40
@TimWilliams nothing's wrong with the first approach, I want them in single lines if possible, just like how it worked without the variables. — Aaron
– Aaron, Commented Aug 15, 2020 at 0:53

Yair · Accepted Answer · 2020-08-15 01:34:30Z

1

You should use ampersand (&) to concatenate two or more variable with string. This worked for me:

Sub test()
    
Const Set1 As String = "1:2,4:5"
Const Set2 As String = "7:8,11:12"
        
Worksheets("Sheet1").Range(Set1 & "," & Set2).EntireRow.Hidden = True
    
End Sub

answered Aug 15, 2020 at 1:34

Yair

381 gold badge1 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VBasic2008 · Accepted Answer · 2020-08-15 06:40:46Z

1

Multiple Ranges

You could study the short article Application.Union method (Excel) | Microsoft Docs. Using this method you could write something like the following:

Sub testUnion()
    
    Const Set1 As String = "1:2,4:5"
    Const Set2 As String = "7:8,11:12"
    Const wsName As String = "Sheet1"
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim rng As Range: Set rng = Union(ws.Range(Set1), ws.Range(Set2))
    rng.EntireRow.Hidden = True
    
End Sub

answered Aug 15, 2020 at 6:40

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Collectives™ on Stack Overflow

Excel string variables not working in range

2 Answers 2

Comments

Multiple Ranges

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Multiple Ranges

Comments

Your Answer

Sign up or log in

Post as a guest

Related