How to get range based on two variables in VBA

Question

I have a last_row and last_column variable.

I get these by doing the following.

last_column = .Cells(1, .Columns.Count).End(xlToLeft).Column

and

last_row = .Range("A" & .Rows.Count).End(xlUp).Row

My question is this, I'm trying to create a selection based on these variables, so that the range goes from E33 to the last row and column. However, I am getting an error. Here is the code.

Range("E33:" & last_column & last_row).Select

Thanks so much for your help!

Answer 1

You can use Cells() with column and row numbers

set myRange = Range(Cells(33, "E"),Cells(last_row,last_column))

Unless you're using this code in a sheet module, make sure you qualify the sheet that the Range and the Cells method address.

Answer 2

Range("E33:" & last_column & last_row).Select

You need to convert the last_column to a string before you use it in the above code. Is this what you are trying to achieve?

Dim ColName As String
Dim rng As Range

ColName = Split(Cells(, last_column).Address, "$")(1)
Set rng = ws.Range("E33:" & ColName & last_row)

In the above code ws is the relevant sheet.

NOTE

1. Avoid the use of .Select. You may want to see How to avoid using Select in Excel VBA
2. Excel column number from column name