Insert new row at bottom of data range

**Shaun Gemiver** · 01-10-2013, 11:46 AM

Hi all

I am looking to create a macro that would repeat the bottom row of a data range directly underneath, and then delete the data contained within certain cells within that row (ie, A50, B50, D50, G50 only).

However, I don't seem to be able to insert a piece of code that tells the macro to repeat the last row, no matter where the last row is located (ie row 50, or row 51).

Is there any way of doing this?

Thanks guys

**Mumps1** · 01-10-2013, 12:12 PM

This code will repeat the last row:

Sub CopyRow()
    Dim bottomA As Integer
    bottomA = Range("a" & Rows.Count).End(xlUp).Row
    Rows(bottomA).Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End Sub

**sbehera** · 01-10-2013, 12:23 PM

try this...may be helpful for what you are looking for

Sub Macro1()

    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.EntireRow.Select
    
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    RNO = ActiveCell.Row
   
    
    Cells(RNO, 2).ClearContents '< CHANGE HERE THE COLUMN NUMBER
    Cells(RNO, 3).ClearContents
    Cells(RNO, 4).ClearContents
    
    Cells(RNO, 1).Select
     
     
    
End Sub

**Richard Buttrey** · 01-10-2013, 12:30 PM

Hi,

If there is nothing below your range then

Sub CopyRowDeleteCells()
    Dim rDelete As Range, lRow As Long
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & lRow).EntireRow.Copy Destination:=Range("A" & lRow + 1)
    Set rDelete = Application.Union(Cells(lRow + 1, "A"), Cells(lRow + 1, "B"), Cells(lRow + 1, "D"), Cells(lRow + 1, "G"))
    rDelete.ClearContents

End Sub