Modify VBA to Include All Worksheets

[fearonc]

I am a rookie with VBA. The following VBA code successfully strips out all rows earlier than 1/1/2017 but only for the active worksheet. I have 30 worksheets in the workbook/file I would like to strip out all earlier dates. I have tried to modify the sequence based on other examples gleaned through internet searches but have come up short. How do I modify VBA code here to capture all the worksheets? Many thanks!!

Sub DeleteFromDate()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Cells.AutoFilter Field:=1, Criteria1:="<1/1/2017"
ALR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

If ALR > 2 Then
Range("A3:A" & LR).SpecialCells(xlCellTypeVisible).Select
Range("A3:A" & LR).Delete
Range("A1").Activate
End If
Cells.AutoFilter
MsgBox "All Finished deleting rows"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

[skywriter]

You need to use code tags.

You can choose to edit your post, highlight the code, click the # in the toolbar which will put code tags around your code, then save the edited post.

[fearonc]

I apologize but I am confused by what you are asking me to do. I looked up code tags and see this:

Please Login or Register  to view this content.

I also do not see a "#" in my toolbar. I have added tags and indented to match how the VBA code appears in my Excel sheet. As I mentioned, this existing VBA code works only on the active sheet and I would like to somehow have this process repeat (loop) automatically for each of the 30 tabs/worksheets in the workbook. Thank you for your patience.

Please Login or Register  to view this content.

[Mumps1]

I assume that row 1 in each sheet contains the column headers. Is this correct? On which row below the headers does your data start, that is, on which row is your first date in column A?

[fearonc]

The first two rows contain "header" information. Date data begins in A3 for all worksheets. Thanks!

[Mumps1]

Try this on a copy of your file:

Please Login or Register  to view this content.

[fearonc]

That works great!!! (as long as there are no other worksheets present that have no date data in column A). Then the VBA stalls and needs debugging on the "SpecialCells" line. Any way to exclude the worksheets that are not stylized with dates in Column A and allow the process to continue? Thanks for what you have provided already!

[Mumps1]

This macro checks to see if cell A3 in each worksheet contains a date. If it does, then it proceeds with the macro. If it doesn't have a date, then it skips that sheet. Hopefully, this works for you.

Please Login or Register  to view this content.

[fearonc]

That works great! Unfortunately I have discovered I have some "hybrid" worksheets intermingled and the VBA still hangs up. Doing a test with a few sample worksheets that have dates in A3 and including a few that do not, the VBA cleans everything up and skips the worksheets with non-dates. Excellent!! My problem is I have other cluttered worksheets present and some don't follow the rules. Is there a way to modify the VBA to include a line that opens/clears only specific worksheets by name? Say "Boston", "New York", "Chicago", etc. and skip the rest? More initial work on my part but targets only the worksheets that I know need cleaning. Thanks again!!!

[Mumps1]

This should work. Modify the array to suit your needs.

Please Login or Register  to view this content.

[fearonc]

Worked like a charm!!!! Many, many thanks!!!

[Mumps1]

You are very welcome.