VB Code AutoFilter Help

D

Desu Nota from Columbus

Well-known Member
Joined
Mar 17, 2011
Messages
556
I am wondering if it is possible to condense the following code using a FOR statement:

Code: 
Sheets("All Assets").Select
    Cells.Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="Line1"
    Cells.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Line1").Paste
    Sheets("All Assets").Select
    Selection.AutoFilter Field:=1, Criteria1:="Line2"
    Cells.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Line2").Paste
    
    Sheets("All Assets").Select
    Selection.AutoFilter Field:=1, Criteria1:="Line3"
    Cells.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Line3").Paste
    
    Sheets("All Assets").Select
    Selection.AutoFilter Field:=1, Criteria1:="Line4"
    Cells.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Line4").Paste
    
    Sheets("All Assets").Select
    Selection.AutoFilter Field:=1, Criteria1:="Line5"
    Cells.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Line5").Paste
    
    Sheets("All Assets").Select
    Selection.AutoFilter Field:=1, Criteria1:="Line6"
    Cells.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Line6").Paste
    
    Sheets("All Assets").Select
    Selection.AutoFilter Field:=1, Criteria1:="Line10"
    Cells.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Line10").Paste
    
    Sheets("All Assets").Select
    Selection.AutoFilter Field:=1, Criteria1:="Flowline"
    Cells.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Flowline").Paste
    
    Sheets("All Assets").Select
    Selection.AutoFilter Field:=1, Criteria1:="Lamination"
    Cells.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Lamination").Paste
    
    Sheets("All Assets").Select
    Selection.AutoFilter Field:=1, Criteria1:="Grinding"
    Cells.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Grinding").Paste
Earlier in the macro I created and named sheets to exactly match Criteria1 (ex. Line1, Line6, Line10, etc.).
Is it possible to code the following statement?

For Criteria1 = "Line1", "Line2",..., "Line6", "Line10"
Select All Visible Cells and paste to Sheet Name = Criteria1
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Don't know if this will work, but try:
Code: 
For i = 1 To 6
    With Sheets("All Assets").Cells
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Line" & i
        .SpecialCells(xlCellTypeVisible).Copy
        Sheets("Line" & i).Paste
        .AutoFilter
    End With
Next i
With Sheets("All Assets").Cells
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="Line10"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Line10").Paste
    .AutoFilter Field:=1, Criteria1:="Flowline"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Flowline").Paste
    .AutoFilter Field:=1, Criteria1:="Lamination"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Lamination").Paste
    .AutoFilter Field:=1, Criteria1:="Grinding"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Grinding").Paste
    .AutoFilter
End With
 
Upvote 0
JackDanIce said:
Don't know if this will work, but try:
Code: 
For i = 1 To 6
    With Sheets("All Assets").Cells
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Line" & i
        .SpecialCells(xlCellTypeVisible).Copy
        Sheets("Line" & i).Paste
        .AutoFilter
    End With
Next i
With Sheets("All Assets").Cells
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="Line10"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Line10").Paste
    .AutoFilter Field:=1, Criteria1:="Flowline"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Flowline").Paste
    .AutoFilter Field:=1, Criteria1:="Lamination"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Lamination").Paste
    .AutoFilter Field:=1, Criteria1:="Grinding"
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("Grinding").Paste
    .AutoFilter
End With
Click to expand...

Fantastic!! Thanks for the help. This aids me with this macro and my general understanding of VB.
 
Upvote 0
WITH tells VBA which object to perform the next list of actions on (I think).

I think it will help massively with your learning and understanding on VBA to read about NOT using '.Select' whenever possible. There have been plently of postings on this forum about it and if you do a general internet search you can find this too. If you compare your original code to mine, it should be fairly straight forward what I've cut out but highly recommend you learn about how to avoid using '.Select'
 
Upvote 0
JackDanIce said:
WITH tells VBA which object to perform the next list of actions on (I think).

I think it will help massively with your learning and understanding on VBA to read about NOT using '.Select' whenever possible. There have been plently of postings on this forum about it and if you do a general internet search you can find this too. If you compare your original code to mine, it should be fairly straight forward what I've cut out but highly recommend you learn about how to avoid using '.Select'
Click to expand...


Thanks for the advice. I will definitely explore the avoidance of .Select now. If you notice anything else on any of my posts (and future posts) please let me know.
 
Upvote 0
I need help reasoning and altering this part of my macro.

Code: 
    Selection.AutoFilter
    Selection.AutoFilter Field:=54, Criteria1:="job"
    Columns("A:A").Select
    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=54, Criteria1:="stub_job"
    Columns("A:A").Select
    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=54, Criteria1:="stub_shift"
    Columns("A:A").Select
    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=54, Criteria1:="shift"
    Selection.AutoFilter
The code brings up the AutoFilter and then selects 3 criteria (stub_shift, job, stub_job), selects all visible cells minus the header row (offset 1,0) and deletes.

In the spirit of trying to avoid .Select how would I change this? I've tried using a Case statement and it worked, but it WAS SUPER SLOW.

The main catch for me in the above code is how do I reference the range (all visible cells except row 1) and then delete them all at once?

This code avoids .Select, but like I said is really slow. I think it delete row by row.
Code: 
Dim lRow As Long
Dim iRow As Long
 
    With Sheets("Sheet1")
        lRow = .Range("BB" & .Rows.Count).End(xlUp).Row
        For iRow = lRow To 1 Step -1
            Select Case .Cells(iRow, "BB").Value
                Case "stub_shift", "job", "stub_job"
                    .Rows(iRow).Delete
            End Select
        Next iRow
    End With
 
Upvote 0
Hi,

As per forum rules, if it's a new post topic (which your last one is), you should be making a new thread.
 
Upvote 0
(PS As it is though, you're right it does delete row by row - I'd favour a filter and delete visible cells approach as it does it all in one go...)
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
386
Peter_SSs
Peter_SSs
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
398
willow1985
willow1985
R
  • Solved
Filter data set to #N/A, skip step entirely if none
Replies
3
Views
308
RoryA
RoryA
C
  • Question Question
How to get my macro to stop at the last recognized line
Replies
6
Views
553
Coco1
C

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top