Macro to delete data and formatting for many rows below row 7

[glennpc]

I have a spreadsheet where I paste in data from another sheet and then I run some

macros. Later I want to delete what I pasted in so I want a macro to do it. The data I'm removing is an Excel table. It begins in row 8, and it has a variable number of rows.

Because I want to get rid of the data and formatting (I have boxes around each cell) I thought about code to delete like 10,000 rows starting in row 8. I had this:

Sub CleanUp()
  Dim iCntr
  For iCntr = 1 To 10000 Step 1
  Rows(1).EntireRow.Delete
  Next
End Sub

But this was no good because it started with row one-- and I have header type stuff in rows 1 through 7.

Is there a macro that would just delete the table (and leave none of its formatting behind)?

 

[vletm]

... better to have backup!

Sub Del_from_8_to_10000()
    Application.ScreenUpdating = False
    a_tab = ActiveSheet.Name
    If MsgBox("Delete rows from 8 to 10000?", vbYesNo, "Delete?") = vbNo Then Exit Sub
    Worksheets(a_tab).Rows("8:10000").Delete
    Worksheets(a_tab).Range("A1").Select
End Sub

 

[glennpc]

Thanks for your help and your swift reply! Much obliged.