Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post Need help to add loop on the sheets

    Hi Experts
    The below code filters the column and set appropriate condition on the sheet and remove the waste data from the report. It is working fine and gives me the result on Sheet named “Sheet3”). I want to add a loop in the below code which will perform the same exercise in the rest of the sheets if available after sheet3. What I am doing currently is keeping on changing the sheet name to “Sheet3” to make the above code workable in case if there are sheets after sheet3. It is bit hectic sometime.

    Sub Macro1()

    Dim lastrow As Long

    lastrow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1").AutoFilter
    ActiveSheet.Range("$A$1:$L$" & lastrow).AutoFilter Field:=3, Criteria1:="<>"
    Range("A2:L" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow .Delete
    Range("A1").AutoFilter
    ActiveSheet.Range("$A$1:$L$" & lastrow).AutoFilter Field:=5, Criteria1:= _
    "AND COMPRISES THE FOLLOWING POSTINGS"
    Range("A2:L" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow .Delete
    Range("A1").AutoFilter
    End Sub
    Regards,
    JD

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    'simplest to understand
    for i=3 to sheets.count
    sheet(i).activate
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1").AutoFilter
    Range("$A$1:$L$" & lastrow).AutoFilter Field:=3, Criteria1:="<>"
    Range("A2:L" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow .Delete
    Range("A1").AutoFilter
    Range("$A$1:$L$" & lastrow).AutoFilter Field:=5, Criteria1:= _
    "AND COMPRISES THE FOLLOWING POSTINGS"
    Range("A2:L" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow .Delete
    Range("A1").AutoFilter
    next i
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  3. #3
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Don

    Thanks for the code. It worked like a Charm!

    Regards,
    JD

Tags for this Thread

Posting Permissions

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