Reverse table resize + clean contents

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Hi

I have an autoexpanding Excel 2016 table that sometimes needs to be reset back to zero (i.e. header + 1 empty row). This works fine, but it will leave some parts of the automatic expansion on the worksheet.

The table has 3 columns with the 2nd column has a colored format + 3rd column has a formula. They both also autoexpand nicely, however when resizing back to start replicated formulas and color formatting will not be cleared. Is there some neat way to clear this as well?

Thanks!

Code:
ActiveSheet.ListObjects("Table10").Resize Range("$B$15:$F$16")

Screenshot (not able to post a pic for some reason):

https://i.imgur.com/X8xxA40.jpg
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
The table has 3 columns with the 2nd column has a colored format + 3rd column has a formula. They both also autoexpand nicely, however when resizing back to start replicated formulas and color formatting will not be cleared. Is there some neat way to clear this as well?

Code:
ActiveSheet.ListObjects("Table10").Resize Range("[COLOR="#FF0000"][B]$B$15:$F$16[/B][/COLOR]")
:confused: That's a little confusing: Columns B:F doesn't compute to 3 columns.

In any case, does this do what you want?
Test with a copy of your workbook.

Rich (BB code):
ActiveSheet.ListObjects("Table10").DataBodyRange.Delete
 
Upvote 0
try this
Code is minimalist and assumes that there is nothing below or to the right of your table

Code:
ActiveSheet.ListObjects("Table10").DataBodyRange.Offset(1).EntireRow.Delete
ActiveSheet.ListObjects("Table10").DataBodyRange.ClearContents
 
Last edited:
Upvote 0
Hello Peter and Yongle!

Thanks for the feedback, both of you! Valid point on the resize range (which was correct) and referring to 3 columns. There are indeed more columns to that table, but those are mostly empty. Peter's code worked in removing colored formatting, however the downside was that all the contents were removed - i.e. also the formula from the 1st row which is replicated with the autoexpansion, was removed.

Yongle's code, however, at the first glance seems to work fine! Will try to test it some more with additional scenarios. Thank you both!

EDIT: Yongle - 1st row should NOT be cleared, otherwise I will destruct the formula and any manual entries on the 1st row is already cleared by another line.
 
Last edited:
Upvote 0
.. the downside was that all the contents were removed - i.e. also the formula from the 1st row which is replicated with the autoexpanding formula was removed.
It appears that is the case, but for me, as soon as I entered new data in the column(s) that the formula referred to, the formula re-appeared. Have you tried that?
 
Upvote 0
You are right. I looked that the formula bar was empty for the first row and didn't even try entering anything. Thanks again, both options look to have worked and I guess time will tell if I need to prefer one to another :)
 
Upvote 0
Hi again!


After some testing and having a look at my hidden columns, running the code provided by Yongle turned out not to be a great option (for the same reason Yongle already suggested) - I do have some buttons and a table below that which I prefer not to move.
This is not the case with a code by Peter, however after having a closer look at it, there is still some hassle with the data entered on the first row and the formula not being present.

Code:
ActiveSheet.ListObjects("Table10").DataBodyRange.Delete

This is what it actually does for me: it resizes the table back to 2 lines (a header + one empty row) with the first row not having the formula (INDEX&MATCH). Hence, the formula functionality on the 1st row is lost (which is obvious as also the formula bar on this row is empty). However somehow with table autoexpansion, the formula is indeed replicated to the second data row out of seemingly nothing. Frankly this behaviour is something I don't really understand, however can this code be amended so that the functionality would not be lost also on the 1st row?

Thanks a lot!
 
Last edited:
Upvote 0
Sorry for the multitude of messages, however there seems to be some sort of a issue with protected worksheet which is causing this. Looks like
Code:
ListRows.Add
populates the 1st row with the formula again...
 
Upvote 0
Test this amended code
- this deletes entire rows within the table data range
- clears all except formulas in first row

Code:
Sub ClearTable()
    Dim tbl As ListObject:      Set tbl = ActiveSheet.ListObjects("Table10")
    Dim rng As Range:           Set rng = tbl.DataBodyRange
    With rng
        On Error Resume Next
        .SpecialCells(xlCellTypeConstants).ClearContents
        .Resize(rng.Rows.Count - 1).Offset(1).EntireRow.Delete
    End With
End Sub

Anythng below the table is now not affected
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top