Coding a dynamic range

[MarkBeck]

I have this code. The formulas in row 4 copy themselves down.

I think this is not running;
Range("c5:bd&(lastrw.value)").Select

What i am trying to do is calculate the results after resize, then paste the results as values. Row 4 is never overwritten, as it contains the formulas.

Thanks

Mark



Sub UpdateCostCalc()

Set ReCalc = ActiveWorkbook.Sheets("Cost")
lastrw = ReCalc.Cells(Rows.Count, "a").End(xlUp).Row

With ReCalc
.Activate
ActiveSheet.Unprotect
.Range("c4:bd4").Copy .Range("c5").Resize(lastrw - 4, 1)
Calculate
Range("c5:bd&(lastrw.value)").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("a1").Select
End With
End Sub

 

[Andrzejek]

Try:

Range("c5:bd[blue]" & (lastrw.value)[/blue]).Select

Although I am not sure about 'bd' in your Range.....

Have fun.

---- Andy

 

[SkipVought]

Try this...

Sub UpdateCostCalc()
    Dim reCalc As Worksheet, lastrw As Long

    Set reCalc = ActiveWorkbook.Sheets("Cost")
    lastrw = reCalc.Cells(Rows.Count, "a").End(xlUp).Row

    With reCalc
        .Unprotect
        .Range("c4:bd4").Copy .Range("c5").Resize(lastrw - 4, 1)
        Calculate
        With .Range("c5:bd" & lastrw.Value)
            .Copy
            .PasteSpecial _
                Paste:=xlPasteValues, _
                Operation:=xlNone, _
                SkipBlanks:=False, _
                Transpose:=False
        End With
        
        .Range("a1").Select
    End With
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[MarkBeck]

Thanks Andrzejek, Thanks Skip.


Skip.

I get the following compile error;

"Invalid qualifier"

for "Lastrw" within

With .Range("c5:bd" & lastrw.Value)

Mark

 

[SkipVought]

With .Range("c5:bd" & lastrw)

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[MarkBeck]

Thanks