Copy Down - But not to the ends of the Earth!

[zsnow_girl]

Hi,

I'm having problems with macros. I'm creating huge files by copying down a formula within the macro to the end of the known universe. (Then I have to go into each file and delete it all by hand which often crashes my PC)

ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-1]"
Range("F1:G1").Select
Selection.AutoFill Destination:=Range("F:G")
Range("F:G").Select

The macro runs through files of differing sizes. So how do I say I only want it to copy to the bottom of the table?

This probably has a simple solution. I just can't find it!

Please help,
Thanks

 

[Trebor76]

Hi zsnow_girl,

The way you've written/recorded your macro makes it hard to follow, but see if my following suggestion helps (I've added some notes to hopefully make it easier to follow):

Sub Macro1()

    'Declare variable.
    Dim lngLastRow As Long
    
    'Set variable _
    Assumes the last row can be found from Column F - _
    change as required.
    lngLastRow = Cells(Rows.Count, "F").End(xlUp).Row
        
    'Enter the formula 'C1-E1' in cell F1 and copy _
    down to the row as defined by the _
    'lngLastRow' variable.
    Range("F1:F" & lngLastRow).Formula = "=C1-E1"

End Sub

HTH

Robert

 

[XLXRider]

Why not name ranges?? Then you copy only what you want and put it where you want!

For this code, name a range "YourData". In fact, make that the cell where your formula resides. Now, name the range to where you wish to place the copied formula "RangeName". Run the macro. There ya go! (To name a range, simply select the cell or cells, then go to the space above cell A1. It will have the designation of the upper leftmost cell in your range. Select that space, the cell name will jump to the left. Now, type in your desired name for the range, such as "SnowGirl" or "Sno_Grl" or whatever you like. Confirm it with the Enter key.)

Sub CopyMe()
    Application.Goto "YourData"
    Selection.Copy
    Application.Goto "RangeName"
    ActiveSheet.Paste
    Application.Goto "YourData"
    Application.CutCopyMode = False
End Sub

Hope you can use this...
Regards,
XLXRider

 

[zsnow_girl]

Hi,
Thanks for the tip - hopefully I can improve most of my macros with this.

My code (or at least some of it's) ended up looking like this. and it works!
Thanks again.

Dim lngLastRow As Long

lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row

'Calculate depth of base of peat
Range("F1").Select
ActiveCell.FormulaR1C1 = "=RC[-5]*(R1C8/2)"

'Calculate OD of base of peat
Range("G1").Select
ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-1]"
Range("F1:G1").Select
Selection.AutoFill Destination:=Range("F1:G" & lngLastRow)
Range("F:G").Select