+ Reply to Thread
Results 1 to 12 of 12

Modify VBA to Include All Worksheets

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    Excel 2010
    Posts
    145

    Modify VBA to Include All Worksheets

    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

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Modify VBA to Include All Worksheets

    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.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  3. #3
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    Excel 2010
    Posts
    145

    Re: Modify VBA to Include All Worksheets

    I apologize but I am confused by what you are asking me to do. I looked up code tags and see this:
    ,
    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.

    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

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,868

    Re: Modify VBA to Include All Worksheets

    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?
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    Excel 2010
    Posts
    145

    Re: Modify VBA to Include All Worksheets

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

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,868

    Re: Modify VBA to Include All Worksheets

    Try this on a copy of your file:
    Sub DeleteFromDate()
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Dim LR As Long
        Dim ws As Worksheet
        For Each ws In Sheets
            LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Range("A2:A" & LR).AutoFilter Field:=1, Criteria1:="<1/1/2017"
            ws.Range("A3:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
        Next ws
        MsgBox "All finished deleting rows."
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End Sub

  7. #7
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    Excel 2010
    Posts
    145

    Re: Modify VBA to Include All Worksheets

    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!

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,868

    Re: Modify VBA to Include All Worksheets

    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.
    Sub DeleteFromDate()
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Dim LR As Long
        Dim ws As Worksheet
        For Each ws In Sheets
            If IsDate(ws.Range("A3")) Then
                LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                ws.Range("A2:A" & LR).AutoFilter Field:=1, Criteria1:="<1/1/2017"
                ws.Range("A3:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
            End If
        Next ws
        MsgBox "All finished deleting rows."
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End Sub

  9. #9
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    Excel 2010
    Posts
    145

    Re: Modify VBA to Include All Worksheets

    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!!!

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,868

    Re: Modify VBA to Include All Worksheets

    This should work. Modify the array to suit your needs.
    Sub DeleteFromDate()
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Dim LR As Long
        Dim ws As Worksheet
        For Each ws In Sheets(Array("Boston", "New York", "Chicago"))
            LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Range("A2:A" & LR).AutoFilter Field:=1, Criteria1:="<1/1/2017"
            ws.Range("A3:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
        Next ws
        MsgBox "All finished deleting rows."
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End Sub

  11. #11
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Eagle River, Alaska
    MS-Off Ver
    Excel 2010
    Posts
    145

    Re: Modify VBA to Include All Worksheets

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

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,868

    Re: Modify VBA to Include All Worksheets

    You are very welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Modify A Formula To Include AND
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  2. Modify A Formula To Include AND
    By Aladin Akyurek in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 09:05 AM
  3. [SOLVED] Modify A Formula To Include AND
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  4. [SOLVED] Modify A Formula To Include AND
    By Aladin Akyurek in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  5. [SOLVED] Modify A Formula To Include AND
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. Modify A Formula To Include AND
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. Modify A Formula To Include AND
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. Modify A Formula To Include AND
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1