CODEWITHSUNDEEP
excelvba
Thorma
Thorma

Reputation: 1

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

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.

Upvotes: 0

Views: 266

Answers (1)

BigBen
BigBen

Reputation: 50007

"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

Upvotes: 1

Related Questions