VBA: Adaptive Selection of Rows, Columns, Range by Variable

Question

I had a hard time selecting a specific headline. Nevertheless I hope someone is able to help me out.

I know that I can reference a Range via a String like this:

    Dim Str as String

    Str = "A1:A10"

    ThisWorkbook.Sheets("Table1").Range(Str).Select
    [...]
    ThisWorkbook.Sheets("Table2").Range(Str).Select
    [...]

But is it also possible to address a specific Range by putting the whole statement between the two dots like 'Range("A1:A10")' into a variable?

It would be handy since I often copy and paste code and sometimes I need to reference 'Rows(1:5)', sometimes 'Columns(A:E)', or a specific 'Range("A1:A10")'. Then I need to go through the whole code and replace these parts.

It would be great to just define this part within a variable in the beginning and use it throughout the script. Something like

    Dim Rfrc As String

    Rfrc = Rows("1:5")
or  Rfrc = Columns("A:E")
or  Rfrc = Range("A1:A10")

    ThisWorkbook.Sheets("Table1").Rfrc.Select
    [...]
    ThisWorkbook.Sheets("Table2").Rfrc.Select
    [...]

This is really my first question here and I hope you could help me out. So far I was always able to find answers to all my questions here, but this time I couldn't.

BigBen · Accepted Answer · 2018-07-19 20:54:41Z

1

"But is it also possible to address a specific Range by putting a whole statement like 'Range("A1:A10")' into a variable?"

Yes - a Range variable, a fundamental part of the Excel VBA toolkit. Instead of referencing a range by its address, Set and then reference a Range variable directly.

Sub WorkWithRanges()
    Dim rng As Range

    ' Work with a specific range, rows or columns
    With Sheets("Sheet1")
        Set rng = .Range("A1:A10")
        Set rng = .Rows("1:5")
        Set rng = .Columns("A:E")
    End With
End Sub

Noting that you normally don't need to Select ranges to work with them and you want to always fully qualify what Worksheet they are on - i.e. try to avoid ActiveSheet.

Edit 1

If you're looping through sheets and it's the same Range on each sheet, set the Range using one reference.

Sub DimARangeOnceOnly()
    Dim ws As Worksheet
    Dim rng As Range

    For Each ws In ThisWorkbook.Worksheets
        With ws
            ' It's the same Range on each Worksheet
            ' And if I need to update the reference I only need to do it once
            Set rng = .Range("A1:A10")
            Set rng = .Rows("1:5")
            Set rng = .Columns("A:E")
            ' ...
        End With
    Next ws
End Sub

If you want to define a reference at the beginning and use it later, here's a String-based approach.

Sub UseAStringForARangeReference()
    Dim rowRfrc As String
    Dim colRfrc As String
    Dim rngRfrc As String
    Dim rng As Range
    Dim ws As Worksheet

    rowRfrc = "1:5"
    colRfrc = "A:E"
    rngRfrc = "A1:A10"

    For Each ws In ThisWorkbook.Worksheets
        With ws
            Set rng = .Range(rngRfrc)
            Set rng = .Rows(rowRfrc)
            Set rng = .Columns(colRfrc)
        End With
    Next ws
End Sub

edited Jul 19, 2018 at 20:54

answered Jul 19, 2018 at 20:13

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

3 Comments

Thorma Over a year ago

Thank you for your fast response. I maybe couldn't state my issue correctly. I know about that option. But i would also like to use Row, Columns sometimes instead of Range. Is that possible. Also the specific Range appears on different sheets in the code. So I can't just set a Range like this since it is always linked to a specific sheet, or?

BigBen Over a year ago

Rows and Columns are ranges too... But when you say "Then I need to go through the whole code and replace these parts" - sounds like more of an overall design issue. If the specific Range appears on different sheets, when you loop through each sheet you can "reset" the Range, with only one reference to it in your code.

BigBen Over a year ago

If you have working code but are trying to improve it, maybe head over to Code Review - as your question is, it's hard to really nail down your issue.

Collectives™ on Stack Overflow

VBA: Adaptive Selection of Rows, Columns, Range by Variable

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related