Paste into Table then delete Rows below last pasted Value

[hmltnangel]

Afternoon All

Cant seem to find anythjing I could use for this, so thought I could ask on here. Hopefully someone has something hiding away that would work.

I copy from one table - to another and paste into "Table2" as text every day. The amount of table rows varies from day to day. Is there a piece of VBA that would allow me to paste in and auto delete any table rows that did not get data pasted into them?

thanks

 

[maabadi]

This is one Example, If your table2 start from row 1, Delete all rows except header and first row after that.

Sub PasteDatatoTable()
Dim Sh1 As Worksheet, Sh2 As Worksheet, Lr1 As Long, i As Long, Lr2 As Long
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
Sh1.Range("A3:D" & Lr1).EntireRow.Delete
Sh2.Range("A1:D" & Lr2).Copy Sh1.Range("A2")

End Sub