Maximising use of AutoFilter

Sub RangeFilter()








Dim ShowRange As Range




With Worksheets("Sheet1")




  .Cells.AutoFilter Field:=4, Criteria1:=">10"
  .Cells.AutoFilter Field:=3, Criteria1:=">9", Operator:=xlOr, Criteria2:="<4"
  .Cells.AutoFilter Field:=5, Criteria1:=">40"
  .Cells.AutoFilter Field:=6, Criteria1:=">6", Operator:=xlOr, Criteria2:="<2"
  With .AutoFilter.Range
    Set ShowRange = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
  End With
  
  
End With




End Sub

I am using the above to identify patient results that I wish to follow up. My question is whether there is a way to select records based either on Field4 or Field3 OR field5 or field6 etc? The above syntax seems to imply criterion1 AND criterion2 and so on.
Much obliged

Re: Maximising use of AutoFilter

When I apply the above macro , it selects only the records that fulfil all of the criteria, whereas I want the records that fulfil any of the criteria. I hope that this makes sense

Re: Maximising use of AutoFilter

Thank you. This seems to work perfectly. I shall 'play' around with it with a real data sheet. Brilliant!

Re: Maximising use of AutoFilter

Hi again. Something weird happens when I use the macro on the "real" thing: although the real data have exactly the same format as the dummytrial and the macro works perfectly with the dummyfile even when I copy the "real" data to it.
When I use the macro as it is written, I get the message Compile error: Expected End Sub. When I add End Sub below the last End If, I get a compile error: Only comments may appear after End Sub, End Function, or End Property.
What have I done or what do I need to do? THanks

Re: Maximising use of AutoFilter

Problem solved! I deleted all modules from the macros that I use for this spreadsheet which is initially received as a csv file. I then reloaded the module for identifying and reporting the results and it now works perfectly. Iguess that I must have had more than one module that conflicted.
A last question on this topic. Is it possible to transfer the results to appear in a formatted way , similar to the message box in appearance, but with formatted text ?