VBA: Delete all rows down to End of Table

[zero269]

Hello,

I've created a test Macro to delete rows from an Excel Table. However, considering the number of rows change after each import, I'm trying to figure out how to delete Row #3 all the way down to the end of the Table... leaving the Total row alone when applicable.

The Macro shows multiple entries for deleting rows(2) over and over again.

    Range("D3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete
    Selection.ListObject.ListRows(2).Delete

Is there a way to tell it to go to the end instead of repeating?

Note: I'm just beginning to play with VBA code...

 

[zero269]

If .Rows.Count > 1 Then

I forgot to mention that I had to change the "> 1" to "> 2" as I needed to keep the first two Table Rows; not Sheet Rows.

For others looking for a cleaner solution, I'd recommend looking at Peters code which I think requires setting the Offset to get the correct Table Rows to start deleting...?

VBA Code:

Sub test2()
  With ActiveSheet.ListObjects("Table1") '<- Adjust to your table name
    With .DataBodyRange
      If .Rows.Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).ClearContents
      .Resize(, .Columns.Count - 1).ClearContents
    End With
    .Resize .Range.Resize(2)
  End With
End Sub

 

[zero269]

I have to missing something.
Just deleting the databody range deletes all the data rows AND leaves any formulas available. (They will reappear as soon as you enter data)

Sub testdelete()

    Dim lo As ListObject

    Set lo = Range("Table1").ListObject
   
    lo.DataBodyRange.Rows.Delete
End Sub

Hi Alex,

Your code is very interesting to say the least... Please see my question at the end of this response.

When I 1st tested it, I noticed the formula in the last column was missing as you stated it would. I mistakenly started typing into that column. I hit ESC, but when I entered data into the first column, the formula didn't reappear.

On my 2nd test, I didn't make that same mistake. I entered some data in the first column and then checked the last column. Lo-and-behold, the formula reappeared.

Because this table is populated using web data, I ran a 3rd test and Refreshed the data using a few rows and was happy to see that the last column with the formula was there for all of the records.

Formula disappeared:

Formula reappeared:

To recap for others: Your code deleted all of the Table Rows in the specified table, leaving only the first two Table Rows; and preserving the Formula that belongs in the last column (in my case), thus producing the desired result.

I'm curious... how is that formula being preserved? I'm new to VBA, but not new to basic programming with PowerShell and batch file scripting for managing Windows systems... and I find this amazing. Your code doesn't seem to leave any hints as to anything being "stored" for later use so to speak.

What do you call that? And is there a way to leverage it for more than one specified Table? or all Tables on a given Sheet?

Thanks...

 

[Peter_SSs]

I forgot to mention that I had to change the "> 1" to "> 2" as I needed to keep the first two Table Rows; not Sheet Rows.

I don't believe that is a correct change as the .1 is referring to the dataBodyRange rows only, not the 'Table rows'. My understanding was that the two table rows that you wanted to keep were the header row and the first row of the DataBodyRange only.
If that is correct then my code should be left at ">1".
To see why, manually remove some rows from one of your tables until there is only the header row and two rows of the DataBodyRange remaining - that is, three table rows altogether.
Now test your code from post #10 on that data.

Having said that, I'm not suggesting that you continue to use any form of my code as the code Alex posted is much simpler and cleaner. I wasn't aware that a method like that for clearing a table existed.
(Great code Alex! )

BTW, your post #10 code

• does not remove the other data from the first data row of the table that you said in post #4 that you wanted to clear.
• does not need the red part in the lines like this since the number of columns will already be correct from the DataBodyRange reference already set above those lines
  .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
• Uses 'Select' a number of times near the end. Select is rarely needed and tends to slow your code. To clear the cells in question, consider this change

Range("A4").Select
Selection.ClearContents
Range("D4:N4").Select
Selection.ClearContents

Range("A4, D4:N4").ClearContents

 

[vcoolio]

Just curious to know if you bothered to read post #6?

Cheerio,
vcoolio.

 

[zero269]

If that is correct then my code should be left at ">1".

Hi Peter,

Yes, you are correct when it comes to your code.

I was offering a comparison between your code and the code I shared in my post (#7) that I got from thespreadsheetguru.com (linked in my post) due to them being very, very similar.

I didn't want other readers to assume they were identical. The original code used a >1 value, but when I first tested it, it deleted all of the rows. I closed my Workbook without saving and then changed the value to 2 to see any differences. It deleted all Table Rows greater than Table Row #2 (Row #2 was untouched), so that led me to believe that the value referred to a Table Row; later to discern there is a difference between a Table Row and a Sheet Row.

After looking at yours, I noticed it's using a different offset value, but honestly, I don't know how to fully interpret any VBA at this point. Only some that I've learned in the last 48 hours... which isn't much.

 

[zero269]

Perhaps this will suffice:-

Sub Test()

    Dim ws As Worksheet: Set ws = Sheets("Your sheet name Here")
  
    With ws.ListObjects("Table1").Range
           Application.Intersect(.Cells, .Offset(2)).Delete
    End With

End Sub

Cheerio,
vcoolio.

Hi vcoolio,

Just saw your post a few minutes ago. I thought I responded yesterday but looks as though I didn't. Sorry about that. I spent nearly all day yesterday tackling this topic and didn't see a resolution until late last night.

I tried running your code against my tbl_BooksSource and it's throwing up a run-time error '9': Subscript out of range

Debug looks like this:

FYI:
Check out the code Alex Blakenburg provided earlier today. I almost ignored it because I already had some working code, but ultimately decided to try it out.

Check out my response to him, you may find it interesting if you're not already familiar with what his code does. I decided to use his because it's short and sweet and actually clears all the Table data Rows but somehow preserves the formulas for the column(s).

 

[Alex Blakenburg]

Thanks Peter

I'm curious... how is that formula being preserved? I'm new to VBA, but not new to basic programming with PowerShell and batch file scripting for managing Windows systems... and I find this amazing. Your code doesn't seem to leave any hints as to anything being "stored" for later use so to speak.

This has nothing to do with VBA it is a table property. I have tried to research how the table stores it but have come up empty handed.
It seems to store the formula and also formatting somewhere. If you get the formatting out of sync you need to restore it on all rows at the same time for it to then be available when you add new rows. It doesn't seem to use either the first row's or last row's formatting for new rows so as you have discovered it has to be stored somewhere else but how you access it ........ ???

 

[zero269]

Hi Peter,

To see why, manually remove some rows from one of your tables until there is only the header row and two rows of the DataBodyRange remaining - that is, three table rows altogether.
Now test your code from post #10 on that data.

I just tried what you suggested and you are correct. When I have only 3 Table Rows, the code doesn't remove anything.
When I had 4 Table Rows, Rows 3 & 4 were removed.

Having said that, I'm not suggesting that you continue to use any form of my code as the code Alex posted is much simpler and cleaner. I wasn't aware that a method like that for clearing a table existed.
(Great code Alex! )

I'm right there with you. I found it rather amazing myself and I know little to nothing about VBA; but do have experience with a few other programming languages as it relates to Windows management, and I've never seen anything remotely similar. It's perplexing... but it's now what I'm using because it literally clears everything without losing my formulas in both tables.

 

[Peter_SSs]

I tried running your code against my tbl_BooksSource and it's throwing up a run-time error '9': Subscript out of range

You didn't read @vcoolio's code carefully enough. The following asks for the sheet name, not the table name.

@vcoolio
I think that your code would need a resize included to avoid deleting the Total row (if it exists)

I'm trying to figure out how to delete Row #3 all the way down to the end of the Table... leaving the Total row alone when applicable.

It may also need a check in case no rows need deleting

 

[zero269]

The following asks for the sheet name, not the table name.

True that!
I just tested vcoolio's code again and it successfully removed all Table Rows greater than 2.