Deleting an entire row range portion during copy and paste work.

[erbhaskar95]

Lets say I have my main excel workbook that contains 2800 rows (filled with data). I copied some information or range of rows from another workbook and pasted it in my main excel workbook. The pasted range was upto 1500 rows only. Now how can I delete the range from one cell below the last pasted cell row (i.e 1501 in this case) to 2800 rows? Sometimes it might be 1400 rows or 1700 rows depending on situations so my main objective is to delete the rows from one cell below the last pasted range upto the end (i.e row number 2800)

 

[Fluff]

Why not clear the sheet first & then paste the new data in?

 

[erbhaskar95]

Clearing the sheet wont help in my case because there are about 20 different columns that contains the formulas for calculations based on what Variable I paste in a dummy cell rows. The values (variables) for the dummy cell rows are brought from another another workbook and then pasted in my main file and when it gets pasted, all my remaining column parameters gets adjusted automatically. Then the last part remains of deleting the unfilled cell rows upto the last and this is where I am getting stuck.

 

[MARK858]

Maybe something like

Sub DelRows()
    Dim FirstRng As Range, FirstRow As Long, LastRow As Long

    ' get range being copied
    Set FirstRng = Sheets("Sheet2").Range("A1").CurrentRegion

    ' get number of rows in FirstRng + 1 row
    FirstRow = FirstRng.Rows.Count + 1

    ' Find original last row in destination sheet
    LastRow = Sheets("Sheet1").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

    ' do the copy
    FirstRng.Copy Sheets("Sheet1").Range("A1")

    ' test if the original number of destination rows
    ' is greater than the number of rows being copied then do the delete
    If LastRow > FirstRow Then Sheets("Sheet1").Rows(FirstRow & ":" & LastRow).Delete

End Sub