CODEWITHSUNDEEP
vbaexcel
lindqmx1
lindqmx1

Reputation: 89

Variable Range Used for a Formula

I recorded the below code for use in my macro, but it would only be useful if that range never changes.

Because the range does change each time I run it, how can I change "A1:E2" into variables to account for a changing range?

I don't think xlLastCell would be a correct usage in this case.

I know how to get the column number and row number of the ending cell, but I couldn't figure out how to incorporate that into my code. 

ActiveCell.Offset(2, 0).Range("A1:E2").Select 
Selection.FormulaR1C1 = "0"

Upvotes: 0

Views: 121

Answers (1)

David Zemens
David Zemens

Reputation: 53623

I know how to get the column number and row number of the ending cell, but I couldn't figure out how to incorporate that into my code.

Like this:

Dim myRange as Range
Set myRange = Range(Cells(1,1), Cells(lastRow, lastColumn))
myRange.FormulaR1C1 = "0"

In the above code, myRange is defined by two cells, one at (1,1) (row 1, column 1) and another at (lastRow, lastColumn) which would be the vairables you identifed as the ending row/column.

Upvotes: 1

Related Questions