CODEWITHSUNDEEP
excelvba
jmaz
jmaz

Reputation: 527

How Do I Put These String Variables into a Range?

The below code assigns number values to the two variables ColumnNumber1 and ColumnNumber2, then coverts them to letters.

Dim ColumnNumber1 as Long
Dim ColumnNumber2 as Long

Dim ColumnLetter1 As String
Dim ColumnLetter2 As String

ColumnNumber1 = 1
ColumnNumber2 = 2

ColumnLetter1 = Split(Cells(1, ColumnNumber1).Address, "$")(1)
ColumnLetter2 = Split(Cells(1, ColumnNumber2).Address, "$")(1)

Now, ColumnLetter1 = A and ColumnLetter2 = B. So far, so good. I want to put these into a range like this:

range("A:B")

but VBA rejects everything I try, such as

range("ColumnLetter1:ColumnLetter2")

What is the correct way to put ColumnLetter1 and ColumnLetter2 into the range format?

Upvotes: 0

Views: 57

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Use this one:

Range(ColumnLetter1 & ":" & ColumnLetter2)

another option is to use column number:

Range(Cells(1, ColumnNumber1), Cells(1, ColumnNumber2)).EntireColumn

both approaches gives you Range("A:B") for ColumnNumber1 = 1 and ColumnNumber2 = 2

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

How about:

Sub marine()
    Dim ColumnNumber1 As Long
    Dim ColumnNumber2 As Long

    Dim ColumnLetter1 As String
    Dim ColumnLetter2 As String

    ColumnNumber1 = 1
    ColumnNumber2 = 2

    ColumnLetter1 = Split(Cells(1, ColumnNumber1).Address, "$")(1)
    ColumnLetter2 = Split(Cells(1, ColumnNumber2).Address, "$")(1)

    Set r = Range(ColumnLetter1 & ":" & ColumnLetter2)
End Sub

Upvotes: 2

Related Questions