Results 1 to 2 of 2

Thread: Help on getting the last row in a range

  1. #1

    Help on getting the last row in a range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,
    PeterSample - Copy Down.xlsx

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •