use variable value in a range reference?
 

My code is

Dim myformulaTotalRows As Integer
Dim mylastFormulaRow As Integer

With Range("formulaCells") 'named range
totalRows = .Rows.Count
lastRow = .Rows(totalRows).Row
End With

myformulaTotalRows = totalRows
mylastFormulaRow = lastRow

Is there a way to use "mylastFormulaRow" in a statement like
Range("C6").select where the row number 6 can be replaced by
"mylastFormulaRow"?

I want to use the selected cell as the first argument in a .filldown statement

Thanks for any help

use variable value in a range reference?
 

Range("C" & mylastformularow).select
or
cells(mylastformularow, "C").select



JCIrish wrote:

My code is

Dim myformulaTotalRows As Integer
Dim mylastFormulaRow As Integer

With Range("formulaCells") 'named range
totalRows = .Rows.Count
lastRow = .Rows(totalRows).Row
End With

myformulaTotalRows = totalRows
mylastFormulaRow = lastRow

Is there a way to use "mylastFormulaRow" in a statement like
Range("C6").select where the row number 6 can be replaced by
"mylastFormulaRow"?

I want to use the selected cell as the first argument in a .filldown statement

Thanks for any help

--

Dave Peterson

use variable value in a range reference?
 

Sub fillitdown()
'Range("e1").AutoFill Range("e1:e4")
dim lr as long
lr = Cells(Rows.Count, "e").End(xlUp).Row
Cells(lr, "e").AutoFill Cells(lr, "e").Resize(4)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JCIrish" wrote in message
...
My code is

Dim myformulaTotalRows As Integer
Dim mylastFormulaRow As Integer

With Range("formulaCells") 'named range
totalRows = .Rows.Count
lastRow = .Rows(totalRows).Row
End With

myformulaTotalRows = totalRows
mylastFormulaRow = lastRow

Is there a way to use "mylastFormulaRow" in a statement like
Range("C6").select where the row number 6 can be replaced by
"mylastFormulaRow"?

I want to use the selected cell as the first argument in a .filldown
statement

Thanks for any help

use variable value in a range reference?
 

Thanks, Dave. I must have tried ten things similar to your solution except
including the &. I appreciate the help.

"Dave Peterson" wrote:

Range("C" & mylastformularow).select
or
cells(mylastformularow, "C").select



JCIrish wrote:

My code is

Dim myformulaTotalRows As Integer
Dim mylastFormulaRow As Integer

With Range("formulaCells") 'named range
totalRows = .Rows.Count
lastRow = .Rows(totalRows).Row
End With

myformulaTotalRows = totalRows
mylastFormulaRow = lastRow

Is there a way to use "mylastFormulaRow" in a statement like
Range("C6").select where the row number 6 can be replaced by
"mylastFormulaRow"?

I want to use the selected cell as the first argument in a .filldown statement

Thanks for any help

--

Dave Peterson
.

use variable value in a range reference?
 

Thank you,Don. That's a more elegant way to go than my Sub. I'm sure it will
be useful to me in many similar situations.

"Don Guillett" wrote:

Sub fillitdown()
'Range("e1").AutoFill Range("e1:e4")
dim lr as long
lr = Cells(Rows.Count, "e").End(xlUp).Row
Cells(lr, "e").AutoFill Cells(lr, "e").Resize(4)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JCIrish" wrote in message
...
My code is

Dim myformulaTotalRows As Integer
Dim mylastFormulaRow As Integer

With Range("formulaCells") 'named range
totalRows = .Rows.Count
lastRow = .Rows(totalRows).Row
End With

myformulaTotalRows = totalRows
mylastFormulaRow = lastRow

Is there a way to use "mylastFormulaRow" in a statement like
Range("C6").select where the row number 6 can be replaced by
"mylastFormulaRow"?

I want to use the selected cell as the first argument in a .filldown
statement

Thanks for any help

.