CODEWITHSUNDEEP
excelvba
Goose44
Goose44

Reputation: 23

How to get range based on two variables in VBA

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!

Upvotes: 2

Views: 687

Answers (2)

teylyn
teylyn

Reputation: 35900

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.

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149277

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

Upvotes: 2

Related Questions