Referencing a cell/range using a variable

[dl7631]

Hello!
I have a simple question.
I have introduced a variable FinalRow - let's say it's row 20.
Now, if I want to refer to a Range("A1:F20") using FinalRow - how should I do it? Will this work:
Range("A1".Offset(RowOffset:=FinalRow, ColumnOffset:=6)?

Thank you!

 

[schielrn]

Are you trying to do this in VBA code I assume? If so, you would want something like:

Range("A1","F" & finalrow)

Also depending on how you have set final row, you may not need the F, but would need to see more of the code and how you declared finalrow?

Hope that helps.

 

[Norie]

Try this.

Range("A1").Resize(FinalRow,6)

 

[dl7631]

Are you trying to do this in VBA code I assume? If so, you would want something like:

Range("A1","F" & finalrow)

Also depending on how you have set final row, you may not need the F, but would need to see more of the code and how you declared finalrow?

Hope that helps.

Yes, it's VBA.
Here is how I defined FinalRow:

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

 

[schielrn]

ADVERTISEMENT

Then what Norie and I have given should be sufficient, it could get rid of the F if the same number of rows is in F as in A by using:

FinalCell = Cells(Rows.Count, "F").End(xlUp)
Range("A1",FinalCell)

Hope that helps.

 

[dl7631]

Thank you very much!

 

[Norie]

schieln

I'm afraid that won't work - FinalCell will only contain value the of the last cell in column F.

If you want to take that approach try this.

FinalCell = Cells(Rows.Count, "F").End(xlUp).Address.

Or this.

Set FinalCell = Cells(Rows.Count, "F").End(xlUp)
Range("A1",FinalCell.Address)