VBA, find last row, copy last row and paste below

[Jeffington]

Hi, I have a problem that seemingly has been asked in one form or another previously, but not exactly as I need, and I just can't seem to be able to gerrymander those answers to fit my needs.

Essentially my issue is simple, I need to find the last row based on Column B, copy the entire row, then paste directly below. I used to be half decent at this sort of stuff, but haven't had to use it for years so have forgotten. Can someone help a VBA illiterate?

 

[Trebor76]

Hi Jeffington,

Welcome to MrExcel!!

This should do the job:

Option Explicit
Sub Macro1()

    Dim wsSrc As Worksheet
    Dim lngRowSrc As Long
    
    Application.ScreenUpdating = False
    
    Set wsSrc = ThisWorkbook.Sheets("Sheet1") '<-Sheet name containing the row to be copied. Change to suit if necessary.
    lngRowSrc = wsSrc.Cells(Rows.Count, "B").End(xlUp).Row
    
    wsSrc.Rows(lngRowSrc).Copy Destination:=wsSrc.Rows(lngRowSrc + 1)
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Jeffington]

That's it! Outstanding. Thank you very much!

 

[Trebor76]

Thanks for letting us know and you're welcome

 

[Jeffington]

Actually, would it be possible to amend/add to the code such that all data (not formatting) is deleted from the line as well, except the data in column B?

 

[Trebor76]

So if you want to only copy the data in the last row of Col. B to the next available row, delete or comment out this line...

wsSrc.Rows(lngRowSrc).Copy Destination:=wsSrc.Rows(lngRowSrc + 1)

...and use this instead:

wsSrc.Range("B" & lngRowSrc).Copy Destination:=wsSrc.Range("B" & lngRowSrc + 1)