How to write VBA code to clear a table but preserve table and 1 empty row

[kwoltman]

Worksheet 1 is called "Instructions:
Worksheet 2 is called "Copy the TXT File Data Here"
The data is formatted as a Table and the table is named "tbl_OpenItems"
The data and table are located on worksheet 2
Within the table "tbl_OpenItems"...
Row 1 is the header row
Row 2-n has data
There are 15 columns (so A-O)
There are no formulas to preserve
When a new users comes into the file, I want them to be able to hit a button that will allow them to clear the old contents which will then allow them to copy in their new data (which will always have a different # of rows)
When the button is hit, I want all data in the table, but NOT the headers to be cleared.
I "think" but I could be wrong, that I need to preserve Row 2 (all blank) so that the User copying in their new data has Cell A2 as their starting point to hit CTRL-V to paste in their data.
The button will be located on Tab 1 called "Instructions" (I may put another version of this button on the worksheet with the table as well, but I'll cross that road later if I do)
The table (tbl_OpenItems) is located on Tab 2 called "Copy the TXT File Data Here"

My apologies as I'm still learning VBA, Macros, etc so I will need precise instructions on how to write this, how and where to save it (i.e. I don't understand when to use or not use Modules), etc. Every detail will be important so I can follow exactly how to implement this. If the code has a Macro name (not sure I'm saying that correctly, but hopefully you know what I mean), then I know how to Assign a button to the Macro so the user can simply click the button to execute the VBA code.

Additional nice to haves (these would be really nice):
- When the button is clicked, a warning should say "Are you sure you want to clear the OpenItems table?" Yes No
- Clicking No pops a new message saying "The operation was cancelled as you requested. Click OK to close and continue where you left off." OK
- Clicking Yes would clear the table and a pop-up message would occur saying "You have cleared the table. Click OK to close this window". OK
- User should be taken to Tab 1 ("Instructions", cell A119) after clicking OK to either the No or Yes pop-up message.

[vcoolio]

Hello Kwoltman,


The following code, assigned to a button, should do the task for you:-

Please Login or Register  to view this content.

Change the table name to suit.

I've attached a sample workbook to show you how this works. Click on the "Delete" button to execute the code.

First, let us know if this suits you then we can do the nitty gritty stuff with helping you to implement it.

I hope that this helps.

Cheerio,
vcoolio.

[kwoltman]

That is what I'm looking for. Now I just need to figure out where it goes (Module?) and how do make that happen.

[bakerman2]

Create a Standard Module and paste the code in it.

Then insert a button from the Forms Collection and assign the macro to it.

Please Login or Register  to view this content.

[kwoltman]

Thank you both!
The only thing that isn't working is the ActiveSheet part (I believe that's the issue)
The button is located on the worksheet called "Instructions"
The Table being cleared is on another worksheet called "Copy the TXT File Data Here"
So when I run it, it bugs out. Debugging highlights this line...

With ActiveSheet.ListObjects("tbl_OpenItem").DataBodyRange '----> Change table name to suit.

I believe I have to tell it the worksheet name and then I still want to return the user to Cell A119 on the "Instructions" worksheet.

Sorry, one more thing...and can we add a "Cancel" option if the following Message boxes occur?

Are you sure you want to delete the current data? (If YES is answered)
Then the next message box is "Data will be cleared. Click OK to continue." OK is the only option here and I think it would help to have a CANCEL option since I'm providing a warning message I feel like I should give the user 1 last chance to stop/cancel the delete.

This is the current code:

Sub ClearOpenItemTable()

Dim lr As Long: lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

With ActiveSheet.ListObjects("tbl_OpenItem").DataBodyRange '----> Change table name to suit.
If MsgBox("Are you sure you want to delete the current data?", vbYesNo, "WARNING") = vbNo Then
MsgBox "Operation has been cancelled. Click OK to continue.", vbExclamation, "Operation cancelled"
Else
MsgBox "Data will be cleared. Click OK to continue.", vbExclamation, "WARNING"
Range("A2:O" & lr).Delete
End If
End With

End Sub

[kwoltman]

See above (please ignore this message)

[kwoltman]

bakerman2...just need a little more help and then I think I'm done.

[bakerman2]

Please Login or Register  to view this content.

[vcoolio]

Hello Kwoltman,

See if the following does all that you need:-

Please Login or Register  to view this content.

I've attached an updated sample for you to play with.

Cheerio,
vcoolio.

[jeffreybrown]

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)