Need help with macro that clears out several different worksheets

[shellecj]

I have created this macro to clear all data from tables on 7 different worksheet tabs; however, the macro aborts if one of the worksheets happens to have been cleared already. Is there a way to avoid this, possibly with an if statement to check if the sheet is empty already (other than header row of course) and if so skip to the next sheet?

Also, I put an onerror goto in the code, with the intent that something went wrong with the macro, that it would skip down to the end and give the error message (rather than the runtime msg with end, abort, etc, which would confuse others when using the macro); however the error msg shows each time, even if the macro was successful? Am I misusing it?

Below is what I have for my code already if someone could take a look at it to make suggestions. Also, if there's anything that could be revised to be more efficient, etc, please advise. I am still very new and learning VBA....Thanks!



Sub Clear_Worksheets()
'
' Clear all worksheets to prepare for importing new data
On Error GoTo Errormessage:
If MsgBox("You are about to clear all data from each tab. Do you want to continue?", vbYesNo + vbExclamation) = vbNo Then
Exit Sub

Else
Sheets("Inventory Status").Visible = True
Sheets("Inventory Status").Unprotect Password:=“password”
Sheets("Inventory Status").Select
'ActiveSheet.Unprotect
'ActiveSheet.ShowAllData
Columns("A:AF").Hidden = False
Worksheets("Inventory Status").AutoFilterMode = False
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Columns("L").Hidden = True
Columns("P:R").Hidden = True
Columns("Y:AA").Hidden = True
Range("AC2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26],Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
Range("A2").Select

Sheets("Compliance").Visible = True
Sheets("Compliance").Unprotect Password:=“password”
Sheets("Compliance").Select
'ActiveSheet.Unprotect
'ActiveSheet.ShowAllData
Worksheets("Compliance").AutoFilterMode = False
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A2").Select

Sheets("Title").Visible = True
Sheets("Title").Unprotect Password:=“password”
Sheets("Title").Select
'ActiveSheet.Unprotect
'ActiveSheet.ShowAllData
Worksheets("Title").AutoFilterMode = False
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A2").Select

Sheets("Eviction").Visible = True
Sheets("Eviction").Unprotect Password:=“password”
Sheets("Eviction").Select
'ActiveSheet.Unprotect
'ActiveSheet.ShowAllData
Worksheets("Eviction").AutoFilterMode = False
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A2").Select

Sheets("Additional Data").Visible = True
Sheets("Additional Data").Unprotect Password:=“password”
Sheets("Additional Data").Select
'ActiveSheet.Unprotect
'ActiveSheet.ShowAllData
Worksheets("Additional Data").AutoFilterMode = False
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A2").Select

Sheets("Weekly Notes").Visible = True
Sheets("Weekly Notes").Unprotect Password:=“password”
Sheets("Weekly Notes").Select
'ActiveSheet.Unprotect
'ActiveSheet.ShowAllData
Worksheets("Weekly Notes").AutoFilterMode = False
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A2").Select


Sheets("Closed").Visible = True
Sheets("Closed").Select
Sheets("Closed").Unprotect Password:=“password”
'ActiveSheet.Unprotect
'ActiveSheet.ShowAllData
Worksheets("Closed").AutoFilterMode = False
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A2").Select

End If

Errormessage: MsgBox "Macro aborted. Check Worksheet...has sheet been cleared already?"

End Sub

[patel]

Sub Clear_Worksheets()
If MsgBox("You are about to clear all data from each tab. Do you want to continue?", vbYesNo + vbExclamation) = vbNo Then Exit Sub
for each Sh in Sheets
with Sh
if .name = "Inventory Status" then
.Visible = True
.Unprotect Password:=“password”
.Columns("A:AF").Hidden = False
.AutoFilterMode = False
.Rows("2:2").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
.Columns("L").Hidden = True
.Columns("P:R").Hidden = True
.Columns("Y:AA").Hidden = True
.Range("AC2").FormulaR1C1 = _
"=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26],Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
.Range("A2").Select
else
.Visible = True
.Unprotect Password:=“password”
.AutoFilterMode = False
.Rows("2:2").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
.Range("A2").Select
End If
end with
End Sub

[mrojas]

The way the On Error is used is as follows

On Error GoTo ErrorTrap

your code goes here

if an error is found, jump to the ErrorTrap block of code
if no errors are found, continue with next line of code
Continue executing lines of code
If all lines of code have been executed, exit sub

ExitPoint: ' This is the code block executed if there are no error or if directed to it

Exit Sub

ErrorTrap:
Some error message or other code to execute before exiting sub
GoTo ExitPoint ' Redirect execution to exit point

End Sub

[SamT]

Sub test()
With ThisWorkbook.Sheets
Rows("2:" & Rows.Count).Delete 'Or ClearContents
'
'
'
End With
End Sub

[shellecj]

Hello All, Sorry for the slow reply. I worked over at work yesterday and did not get home until late. Thank you so much for the suggestions above however I've been playing around with the different suggestions provided for the last couple hours but none seem to fix my problem. :dunno

For the first option provided above by Patel, the code is giving me an error stating For without Next. My code started with and if/end if but if rewriting the code using a For loop then where do I put the Next in my code?

For the clarification on the Error Trapping by mrojas, if I put my code in the ErrorTrap section, which in my case is an error msg, what then goes next to ExitPoint?

The last option provided by SamT does seem to work as well however I run into the same problem as my original code...if I go to run the macro and one of the sheets has already been cleared out (say manually) then again I get the runtime error. Error message I get is ""Run-time error '1004': Delete method of Range class failed

[mrojas]

You're missing your closing Next.
This should appear, from the bottom up, End With and End Sub
That should probably take care of the For without Next

All errors trapped should GoTo ExitPoint once you've dealt with as you see fit.

[mrojas]

Correction: This should appear, from the bottom up, between End With and End Sub

[shellecj]

No go...got an End With without With...sorry :(

[shellecj]

My code initially started with an if, then else, end if...but the first suggestion it was suggested to start with For Each but only the beginning of my original macro was revised...I just don't know how to modify the rest of it? The initial macro I posted did work but only if each of the worksheets (there are 7 total) still have data on them but if any of them happened to be cleared out, I get the run time error and the code aborts without finishing. What I need is the code to skip over the sheet and go on to the next if it happens to be cleared out already?

[ZVI]

Try this:


Sub Clear_Worksheets()

Const PWD = "password"
Dim Sh As Worksheet

' Turn blinking off
Application.ScreenUpdating = False

' Trap errors
On Error GoTo exit_

' Main
For Each Sh In Worksheets
With Sh
' Provide common actions for all sheets
.Visible = True
.Unprotect Password:=PWD
.AutoFilterMode = False
.UsedRange.Offset(1).EntireRow.Delete
' Activate/select the sheet before its cell selection
.Activate
.Range("A2").Select
' Format sheet "Inventory Status"
If StrComp(.Name, "Inventory Status", vbTextCompare) = 0 Then
.Columns("A:AF").Hidden = False
.Columns("L").Hidden = True
.Columns("P:R").Hidden = True
.Columns("Y:AA").Hidden = True
.Range("AC2").FormulaR1C1 = _
"=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26],Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
End If
End With
' Shrink the used range
With Sh.UsedRange: End With
Next

' Activate by-default-sheet
Worksheets("Inventory Status").Activate

exit_:

' Restore screen updating
Application.ScreenUpdating = True

' For error show its details
If Err Then MsgBox Err.Description, vbCritical, "Error #" & Err.Number


End Sub

[mrojas]

Can you post your new code?

[SamT]

Sub SamT()
Const PW As String = "password"

If Not MsgBox("You are about to clear all data from each tab. Do you want to continue?", _
vbYesNo + vbExclamation) Then Exit Sub
'Error handling example
On Error GoTo OOPS

'With all sheets at once. If you have Charts, 'better use "With ThisWorkbook.Worksheets"
With ThisWorkbook.Sheets
Unprotect Password:=PW
Columns.Visible = True
Rows.Visible = True
Rows("2:" & Rows.Count).ClearContents 'Or .Delete
End With '
'
With Sheets("Inventory Status")
.Columns("L").Hidden = True
.Columns("P:R").Hidden = True
.Columns("Y:AA").Hidden = True
.Range("AC2").FormulaR1C1 = _
"=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26]," _
& "Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
End With

'More code before Error handler here.

'If no Error happens this Exit Sub will run.
Exit Sub

'Code here will never run because it is after the Exit Sub
'and before the GoTo Label.

OOPS:
MsgBox "An Error occurred"

End Sub

[mrojas]

I think it's almost there. I would make one minor change to
End With

'More code before Error handler here.

ExitPoint:
'If no Error happens this Exit Sub will run.
Exit Sub

OOPS:
MsgBox "An Error occurred"

GoTo ExitPoint
End Sub


Any errors generated above the "End With" will be directed to the OOPS label. Below this label you execute whatever code you deem appropriate, maybe just a message box.
Then the next line (Goto ExitPoint) re-directs execution to the ExitPoint label. Execution continues with the following statement, the Exit Sub.

End of Story

[shellecj]

Hi All! Thank you so much for all the help.

ZVI...I did try your code but it deletes all worksheets which I don't want...just specific ones. SamT, I tried yours as well but results in an error (says sub or function not defined). And thank you mrojas, I will keep what you're saying in mind and try to go back and set it up in the sequence you're suggesting.

I'm going to go ahead and attach a workbook at this point. There's a button on the Inventory Status that says "Clear All Sheets" which is attached to the first macro I started with, "Clear_Worksheets" I've went through and scrubbed all sensitive data everywhere (I hope! LOL).


Again, I am very new to VBA :confused4, so bear with me.

Thanks again!

[SamT]

I use that construct when I am working with User Defined Objects: Dictionaries, Collections, Forms, Files, et al. Things that maybe won't be removed from memory on End Sub. Even then I usually use


End With

'More code before Error handler here.


'If no Error happens this GoTo will run.
Goto CleanExit

OOPS:
MsgBox "An Error occurred"

CleanExit:
'Code to clean up any left over bits from memory.

End Sub

In this particular code, it is not needed.

IMHO.

YMMV.

:beerchug:

[mrojas]

Cleaning up is a good habit to have.:friends:

[shellecj]

Cleaning up is a good habit to have.:friends:

yes indeed! :shhh: ;-)

[ZVI]

ZVI...I did try your code but it deletes all worksheets which I don't want...just specific ones.
Well, this code works only in the specific sheets defined by constant MYSHEETS


Sub Clear_Worksheets_4()

Const PWD = "password"
Const MYSHEETS = "Inventory Status,Compliance,Title,Eviction,Additional Data,Weekly Notes,Closed Inventory"
Dim x

If MsgBox("WARNING! You are about to clear all data from each tab." & vbLf _
& " Do you want to continue?", vbYesNo + vbExclamation) = vbNo Then Exit Sub


' Turn blinking off
Application.ScreenUpdating = False

' Trap errors
On Error GoTo exit_

' Main
For Each x In Split(MYSHEETS, ",")
With Sheets(Trim(x))
' Provide common actions for the sheet
.Visible = True
.Unprotect Password:=PWD
.AutoFilterMode = False
.UsedRange.Offset(1).EntireRow.Delete
.Activate
.Range("A2").Select
' Format the sheet "Inventory Status"
If StrComp(.Name, "Inventory Status", vbTextCompare) = 0 Then
.Columns("A:AF").Hidden = False
.Columns("L").Hidden = True
.Columns("P:R").Hidden = True
.Columns("Y:AA").Hidden = True
.Range("AC2").FormulaR1C1 = _
"=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26],Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
End If
End With
' Shrink the used range
With Sheets(x).UsedRange
' Do nothing - it's enough
End With
Next

' Activate by-default-sheet
Worksheets("Inventory Status").Activate

exit_:

' Restore screen updating
Application.ScreenUpdating = True

' For error show its details
If Err Then MsgBox Err.Description, vbCritical, "Error #" & Err.Number

End Sub

[shellecj]

Hello ZVI,

For some reason the code is backing out on the Additional Data tab. Error given is Error #1004 Delete method of Range class failed

Also, just curious, but what does this part of the code do, For Each x In Split(MYSHEETS, ",")
With Sheets(Trim(x)) ? If you don't mind my asking? And is there supposed to be a variable type declared for x?

[ZVI]

Error can happen at trying of deleting the single row of the table.
To avoid such case replace the code line .UsedRange.Offset(1).EntireRow.Clear by .UsedRange.Offset(1).Clear

Taking into account the value of constant MYSHEETS the Split(MYSHEETS, ",") is equal to
Array("Inventory Status", "Compliance", "Title", "Eviction", "Additional Data", "Weekly Notes", "Closed Inventory").
See VBA help of Split function for more details.

The type of variable x is Variant.
If type of variable is not mentioned in Dim statement then it is Variant.
You can use Dim x As Variant as well as Dim x

Variable x in For Each x ... Next loop gets each value from the above mentioned Array.
Firstly x equals to "Inventory Status", then "Compliance" and so on.
And Sheets(Trim(x)) is the same as Sheets("Inventory Status") , then Sheets("Compliance") and so on

[shellecj]

Hi again,

I'm sorry, but I'm still having problems with the code due to hidden columns, hidden rows and in scenario's where the sheet is blank.

If I take out the .EntireRow the code will prompt me asking me if I want to delete the entire row. If I hit YES I get an Error # 1004 This Operation is attempting to shift cells in a table on your worksheet. And will exit out of the macro. I think it's because several columns are hidden and I get this error message when go in to the sheet to manually clear the sheet (even after turning off the filter to show all records). Strangely it does still delete the filtered rows but NOT the hidden rows and the macro still ends with this message. If i hit NO I still get this message but no records at all were deleted. Basically the macro aborts with the #1004 message that operation not all allowed...attempting to shift cells in a table on your worksheet.

I'm also still getting an error if any of the worksheets are already blank...forget exactly what msg said, something Delete Range Method Failed.

And thanks for the clarification on the Split function! So basically it works like the text to columns feature within excel to break up each sheet name by itself? Very Clever! Thanks!

[SamT]

Can you attach a sample workbook. Just replace any personal or proprietary information with dummy info.

I can't understand why you are getting method fail errors just because you have previously deleted some rows. All deleted rows are instantly replaced with new rows.

You will have to "Go Advanced" to attach files. Use "Manage Attachments" at the bottom of the page.

[shellecj]

Hi Sam, I did post a workbook,; it's a couple posts back now. It was posted before ZVI's last suggested code so that would need to still be copied into the module to test it. If you need more information from me though, just let me know? Thanks!

[SamT]

Sub SamT()
Const PW As String = "password"
Dim WkSht As Worksheet 'Won't work on Charts
Dim LNum As Integer 'For Troubleshooting. Remove all when sub works

If Not MsgBox("You are about to clear all data from each tab. Do you want to continue?", _
vbYesNo + vbExclamation) = vbYes Then Exit Sub
'Error handling example
On Error GoTo OOPS
Application.ScreenUpdating = False 'Prevents Flickering

For Each WkSht In ThisWorkbook.Sheets
With WkSht
: LNum = 1
.Unprotect Password:=PW
:LNum = 2
.Columns.Hidden = False
:LNum = 3
.Rows.Hidden = False
:LNum = 4
.Rows("2:" & Rows.Count).ClearContents 'Or .Delete
End With
Next WkSht

With Sheets("Inventory Status")
: LNum = 5
.Columns("L").Hidden = True
.Columns("P:R").Hidden = True
.Columns("Y:AA").Hidden = True
:LNum = 6
.Range("AC2").FormulaR1C1 = _
"=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26]," _
& "Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
End With

GoTo CleanExit

OOPS:
MsgBox "Line # " & LNum & Err.Number & Err.Description

CleanExit:
Set WkSht = Nothing
Application.ScreenUpdating = True
End Sub

[ZVI]

If I take out the .EntireRow the code will prompt me asking me if I want to delete the entire row.
Hi, the suggestion was in using Clear instead of Delete :)
Like this:


Sub Clear_Worksheets_4()

Const PWD = "password"
Const MYSHEETS = "Inventory Status,Compliance,Title,Eviction,Additional Data,Weekly Notes,Closed Inventory"
Dim x

If MsgBox("WARNING! You are about to clear all data from each tab." & vbLf _
& " Do you want to continue?", vbYesNo + vbExclamation) = vbNo Then Exit Sub


' Turn blinking off
Application.ScreenUpdating = False

' Trap errors
On Error GoTo exit_

' Main
For Each x In Split(MYSHEETS, ",")
With Sheets(Trim(x))
' Provide common actions for the sheet
.Visible = True
.Unprotect Password:=PWD
.AutoFilterMode = False
.UsedRange.Offset(1).Clear ' <--- Clear instead of Delete
.Activate
.Range("A2").Select
' Format the sheet "Inventory Status"
If StrComp(.Name, "Inventory Status", vbTextCompare) = 0 Then
.Columns("A:AF").Hidden = False
.Columns("L").Hidden = True
.Columns("P:R").Hidden = True
.Columns("Y:AA").Hidden = True
.Range("AC2").FormulaR1C1 = _
"=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26],Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
End If
End With
' Shrink the used range
With Sheets(x).UsedRange
' Do nothing - it's enough
End With
Next

' Activate by-default-sheet
Worksheets("Inventory Status").Activate

exit_:

' Restore screen updating
Application.ScreenUpdating = True

' For error show its details
If Err Then MsgBox Err.Description, vbCritical, "Error #" & Err.Number

End Sub

[shellecj]

Hi Sam & ZVI!

Thanks for the quick reply! I tried both and both worked the same with the same end result. The problem with clearing contents is that I have all of data on each of the worksheet set up in tables. So if I just clear contents, the range remained the same size, it needs to shrink back to only 1 row, in preparation of copy and pasting all new fresh data to the sheet each week (each week I pull new data out of our system). It also clears all the existing format as well as any existing formulas that were in the far right of several of the worksheets. When deleting the rows, this is not a problem because excel remembers the formula that was there and puts them right back in when copying in a new fresh range of data in cell A2. Also, in both code examples, I filtered some of the data on the Inventory tab to test to see if it would unfilter and delete all rows but unfortunately both code examples left residual rows of data that was previously hidden. Sam, your macro also clears out all worksheets. I only need the aforementioned ones cleared. The Property Lookup, Summary, ABC, SBC, as well as the worksheet with the pivot table set up on the Eviction-Redemption Breakdown tab.

I hope I'm not frustrating you all yet! I do appreciate all the help!


-Michelle

[snb]

removing the Table's databodyrange, without removing column fomulae:


Sheets("eviction").ListObjects(1).DataBodyRange.EntireRow.Delete

Structuring precedes coding; so avoid to put any items in the row of the databodyrange that shouldn't be removed.

[ZVI]

Then may be this:


Sub Clear_Worksheets_5()

Const PWD = "password"
Const MYSHEETS = "Inventory Status,Compliance,Title,Eviction,Additional Data,Weekly Notes,Closed Inventory"
Dim x

If MsgBox("WARNING! You are about to clear all data from each tab." & vbLf _
& " Do you want to continue?", vbYesNo + vbExclamation) = vbNo Then Exit Sub


' Turn blinking off
Application.ScreenUpdating = False

' Trap errors
On Error GoTo exit_

' Main
For Each x In Split(MYSHEETS, ",")
With Sheets(Trim(x))
' Provide common actions for the sheet
.Visible = True
.Unprotect Password:=PWD
.AutoFilterMode = False
If .ListObjects.Count Then
If .ListObjects(1).ListRows.Count Then .ListObjects(1).DataBodyRange.Delete
Else
.UsedRange.Offset(1).EntireRow.Delete
End If
.Activate
.Range("A2").Select
' Format the sheet "Inventory Status"
If StrComp(.Name, "Inventory Status", vbTextCompare) = 0 Then
.Columns("A:AF").Hidden = False
.Columns("L").Hidden = True
.Columns("P:R").Hidden = True
.Columns("Y:AA").Hidden = True
.Range("AC2").FormulaR1C1 = _
"=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26],Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
End If
End With
' Shrink the used range
With Sheets(x).UsedRange
' Do nothing - it's enough
End With
Next

' Activate by-default-sheet
Worksheets("Inventory Status").Activate

exit_:

' Restore screen updating
Application.ScreenUpdating = True

' For error show its details
If Err Then MsgBox Err.Description, vbCritical, "Error #" & Err.Number

End Sub

[shellecj]

Hi All, on this one I didn't get any error and I cleared the one of the sheets out already before running the macro as a test run (before this was a problem as well...macro would abort if any sheet was cleared already) and it only deletes the desired sheets however if there are still hidden rows, only the filtered rows (showing on the sheet) get deleted but the hidden rows are still there. After the macro is complete and I go to the sheet, I can see there's a filter still active...I go to clear it and the remaining rows show up. Is there a way that we can modify this code to start at row 2 and use the xldown method of selecting the rows to the bottom of the range and delete the rows that way? The initial code I started with did do that but it's just that I ran into problems if one of the sheets got manually cleared already, causing the macro to abort instead of continuing on to the other sheet tabs.

Thanks,

Michelle

[ZVI]

Michelle, if hidden rows means those hidden by casual autofilter, then in the code
after the line: .Unprotect Password:=PWD
add this line: If .FilterMode Then .ShowAllData

And to show all data of ListObjects, in the code
after the line: If .ListObjects.Count Then
add this one:.ListObjects(1).AutoFilter.ShowAllData

The revised code:


Sub Clear_Worksheets_6()

Const PWD = "password"
Const MYSHEETS = "Inventory Status,Compliance,Title,Eviction,Additional Data,Weekly Notes,Closed Inventory"
Dim x

If MsgBox("WARNING! You are about to clear all data from each tab." & vbLf _
& " Do you want to continue?", vbYesNo + vbExclamation) = vbNo Then Exit Sub


' Turn blinking off
Application.ScreenUpdating = False

' Trap errors
'On Error GoTo exit_

' Main
For Each x In Split(MYSHEETS, ",")
With Sheets(Trim(x))
' Provide common actions for the sheet
.Visible = True
.Unprotect Password:=PWD
If .FilterMode Then .ShowAllData
If .ListObjects.Count Then
If .ListObjects(1).ShowAutoFilter Then .ListObjects(1).AutoFilter.ShowAllData
If .ListObjects(1).ListRows.Count Then .ListObjects(1).DataBodyRange.Delete
Else
.UsedRange.Offset(1).EntireRow.Delete
End If
.Activate
.Range("A2").Select
' Format the sheet "Inventory Status"
If StrComp(.Name, "Inventory Status", vbTextCompare) = 0 Then
.Columns("A:AF").Hidden = False
.Columns("L").Hidden = True
.Columns("P:R").Hidden = True
.Columns("Y:AA").Hidden = True
.Range("AC2").FormulaR1C1 = _
"=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26],Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
End If
End With
' Shrink the used range
With Sheets(x).UsedRange
' Do nothing - it's enough
End With
Next

' Activate by-default-sheet
Worksheets("Inventory Status").Activate

exit_:

' Restore screen updating
Application.ScreenUpdating = True

' For error show its details
If Err Then MsgBox Err.Description, vbCritical, "Error #" & Err.Number

End Sub

[shellecj]

k...give me a few...am trying this now

[Aussiebear]

k...give me a few...am trying this nowWell I'm off to grab some beers then....

[shellecj]

Well I'm off to grab some beers then....

LOL! Joining in in the show? :beerchug:

[shellecj]

ZVI,

This worked PERFECT! Ran through it several times and testing it's limit by filtering out some records on each sheet and with one sheet completely empty already, and no errors and code cleared all the desired sheets w/out leaving any residual records behind! I had thought in the original code where it said ".AutoFilterMode= False" was sufficient and supposed to take care of that issue but guess not? When I added in the .AutoFilterMode = False, it worked perfectly!

Just 2 quick questions though, do I still leave in the line, Autofiltermode=false in or is that line redundant now? And also, why does the code ask me for confirmation to clear entire row when moving on to each sheet? Is that necessary or is this method just preferable to avoid possible error (as you had mentioned in a previous post)?

Again, thank you all to each and everyone who pitched in with your suggestions! Much appreciated!! :)

[ZVI]

Glad it works!
I've updated the code of Clear_Worksheets_6, please copy it again.
The revised line is: If .ListObjects(1).ShowAutoFilter Then .ListObjects(1).AutoFilter.ShowAllData

The code line Autofiltermode=false is redundant now.


why does the code ask me for confirmation to clear entire row when moving on to each sheet? It should not ask, what is Excel’s version?
Please attach the example with such behavior, or just add this code line on the top of Sub: Application.DisplayAlerts = False
and this line before the end of sub: Application.DisplayAlerts = True

[shellecj]

I am using Microsoft Office Professional Plus 2010 (32-bit). I don't know what you mean by attach an example with such behavior...you mean a screen shot or something like that?

I put the suggested line of code, Application.DisplayAlerts = False right before the screenupdating=false line and turned it back on by putting again right after the line Application.DisplayAlerts = True at the bottom. This did the trick though! :)

[ZVI]

Michelle, thank you for the details.

Asking for deleting of entire rows does not happen in your previous workbook with the updated code.
Therefore I was about attaching the new workbook with that behavior (asking for deleting) to analyze it.
But if DisplayAlerts eliminates that disadvantage then all is good.

Hope the task is solved.

Happy coding!
Vladimir

[shellecj]

Well I have no problem leaving that line of code in there so if all is working good at this point, I'll think I'll just leave it where it's at from here! ;-)

I'm going to go ahead and mark this post as solved but stay tuned...you may see more posts from me soon! I was going to start a new thread soon as to why one of the macros (in module1) is running crazy slow lately...or the odd behavior as to why 'undo' suddenly quit working for this workbook one day.


Stay tuned and thanks again! :clap:

Michelle

[ZVI]

Wish you were here :content:
Feel free to ask questions, someone will get pleasure to help you.

[SamT]

Michelle,

We are really glad we were able to help you solve this problem. If you find any more issues you need help with, please start a new thread just about those, because it gets hard to navigate and understand very long threads.

Also please include all information at the beginning of the thread and attach an example whenever possible. You have seen how much faster a solution is offered when we can see it and test our solutions against the attachment.

Also, please, please, provide all relevant accurate information in the first post of your question.

Post 1 quote:
macro to clear all data from tables on 7 different worksheet tabs

Post 9 quote:
only if each of the worksheets (there are 7 total) still have data on them

Post 26 quote:
The problem with clearing contents is that

Post 29 quote:
still hidden rows, only the filtered rows (showing on the sheet) get deleted but the hidden rows are still there

Post 30 by ZVI quote:
Michelle, if hidden rows means those hidden by casual autofilter And the offered solution worked perfectly.

And finally, from the Jedi Master, snb:
Structuring precedes coding

I hope this lesson helps you get solutions to your problems much faster in the future.