Filter by two criteria

[rickblunt]

Hello,
I have been using this code to sort out a worksheet and pass the results to another sheet, and it works fine. But I was thinking of using this to sort another WS, except that I need to sort this one by two criteria's: column 7 is blank, but column 5 would not be blank. I am unsure of how to tweak the code to add in the second sorting action, all of the ways I tried have not worked. Is it possible to sort by two criteria's?

I appreciate any input - thanks,

Dim rng As Range    
    With Sheets("Repair Log")
        Set rng = .Range("A1:G" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
    With rng
        .AutoFilter Field:=7, Criteria1:=""
        .SpecialCells(xlCellTypeVisible).Copy
        Sheets("Repair Report").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        Sheets("Repair Report").Cells.EntireColumn.AutoFit
    End With

 

[jasonb75]

I am unsure of how to tweak the code to add in the second sorting action, all of the ways I tried have not worked. Is it possible to sort by two criteria's?

You can manually filter by 2 criteria which means that you can do it by code as well. If you try filtering manually with the macro recorder running then it will give you the base code that you need.

 

[Fluff]

How about

Dim rng As Range   
    With Sheets("Repair Log")
        Set rng = .Range("A1:G" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
    With rng
        .AutoFilter Field:=7, Criteria1:=""
        .AutoFilter Field:=5, Criteria1:="<>"
        .SpecialCells(xlCellTypeVisible).Copy
        Sheets("Repair Report").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        Sheets("Repair Report").Cells.EntireColumn.AutoFit
    End With

PS, this filtering, not sorting.

 

[rickblunt]

How about

Dim rng As Range  
    With Sheets("Repair Log")
        Set rng = .Range("A1:G" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
    With rng
        .AutoFilter Field:=7, Criteria1:=""
        .AutoFilter Field:=5, Criteria1:="<>"
        .SpecialCells(xlCellTypeVisible).Copy
        Sheets("Repair Report").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        Sheets("Repair Report").Cells.EntireColumn.AutoFit
    End With

PS, this filtering, not sorting.

(doh! yes, filtering - thinking that, but not saying that. lol)

Thanks Fluff - I was so close to getting it right without having to ask That worked perfectly, I really appreciate the assistance again.

Here's what I tried...

.AutoFilter Field:=7, Criteria1:="", .AutoFilter Field:=5, Criteria1:<>""

.AutoFilter Field:=7, Criteria1:="",
.AutoFilter Field:=5, Criteria1:<>""
and a few others...

 

[Fluff]

Glad we could help & thanks for the feedback.