VBA: Selecting range by variables

vba variables range selection
Fabian Stolz picture Fabian Stolz · Aug 30, 2012 · Viewed 644k times · Source

I want to select the formatted range of an Excel sheet. To define the last and first row I use the following functions:

lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

In the next step I want to select this area: Formula should look like this:

Range(cells(1, 1), cells(lastRow, lastColumn).Select

However, this is not working. Maybe somebody has an idea what is wrong with it. Thanks a lot!

Answer

Nikesh Rastogi picture Nikesh Rastogi · Nov 30, 2012

I recorded a macro with 'Relative References' and this is what I got :

Range("F10").Select
ActiveCell.Offset(0, 3).Range("A1:D11").Select

Heres what I thought : If the range selection is in quotes, VBA really wants a STRING and interprets the cells out of it so tried the following:

Dim MyRange as String
MyRange = "A1:D11"
Range(MyRange).Select

And it worked :) ie.. just create a string using your variables, make sure to dimension it as a STRING variables and Excel will read right off of it ;)

Following tested and found working :

Sub Macro04()

Dim Copyrange As String

Startrow = 1
Lastrow = 11
Let Copyrange = "A" & Startrow & ":" & "D" & Lastrow
Range(Copyrange).Select
End Sub

Related questions

Is it possible to declare a public variable in vba and assign a default value?

I want to do this but it won't compile: Public MyVariable as Integer = 123 What's the best way of achieving this?

Read More
VBA Check if variable is empty

I have an object and within it I wanna check if some properties is set to false, like: If (not objresult.EOF) Then 'Some code End if But somehow, sometimes objresult.EOF is Empty, and how can I check it? …

Read More
Excel - VBA : pass variable from Sub to Userform

I have read and applied solution I found on similar topics but nothing seem to work in my case. So, I want to pass a variable from one sub of my Module1 to a userform. It's a string called "provinceSugg". …

Read More