Help on getting the last row in a range

P

peter.abing

New member
Joined
Oct 24, 2012
Messages
34
Reaction score
0
Points
0
I have an excel spreadsheet with data in several columns. I also have a formulas in another columns that have reference to the former.
Now, I want to copy and paste the formulas up to the last row of the spreadsheet. There is no fixed number of rows. It all depends on the extracted spreadsheet from the system.

How do I proceed in creating an excel macro that will do this. Sample spreadsheet is attached. I just made it simple so classified data is not revealed.

Thanks,
PeterView attachment Sample - Copy Down.xlsx
 
This should probably be in the VBA forum, what you need is a macro not a simple formula.
I have needed this same kind of thing several times.
What you need to do is insert a new module with the folowing code.

Code: 
Sub Macro1()
'
' Macro1 Macro
'


'
    'initalize variables
    Dim iRowEnd As Long    
        
    'calculate last row to use and set formulas on row 2.
    With ActiveSheet
      iRowEnd = .Cells(.Rows.Count, "A").End(xlUp).Row        
      
      Range("F2").Formula = "=A2*1"
      Range("G2").Formula = "=B2*1"
      Range("H2").Formula = "=C2*1"
      Range("I2").Formula = "=D2*1"          
      
       'copy formulas and paste to end of data range
      Range("F2:I2").Copy
      Range("F3:I" & iRowEnd).PasteSpecial 
    
    End With
    
End Sub

Hope this helps
 
You must log in or register to reply here.
Back
Top