Filter between values

[Yokiboha]

I am sure this is quite simple, but it is defeating me.

I have a range of data which includes one column with numbers between 1.10 and 1.99 and need to filter the values between 1.5 and 1.75.

I would be very grateful if someone can help.

 

[L. Howard]

Try this.

With a cell in the list selected, Data tab > Filter > Click on down arrow > click on the box (Select All) > Click Number Filters > Select Between > Follow the prompts of Greater than and Less than windows > OK

Howard

 

[Yokiboha]

Many thanks Howard, it works, but I want to filter the data to another location, is this possible?

 

[L. Howard]

Give this a try in the sheet module.

Assumes the data is in column F.
Copies to column A on active sheet and to column A on sheet 3.

Howard

Option Explicit

Sub A_Filter_Copy()

Application.ScreenUpdating = False
ActiveSheet.Range("F1", Range("F1").End(xlDown)).AutoFilter Field:=1, Criteria1:=">=1.50", _
   Operator:=xlAnd, Criteria2:="<=1.75"

   ActiveSheet.AutoFilter.Range.Copy
   Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
   Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
   
Selection.AutoFilter
Application.ScreenUpdating = False
Application.CutCopyMode = False

End Sub

 

[Yokiboha]

Howard, Many thanks it does it!

I am very grateful

Best regards

Yokiboha

 

[L. Howard]

You are welcome.

Howard

 

[L. Howard]

Hi Yokiboha ,

Just to add, should you want to be able to change the filter values from the sheet, you could us something like this.

Where You have drop downs in cells H1 and H2 and you select these to be the "Filter Between Values".

Or you could just type the values into H1 and H2 without the drop downs.

Howard

Option Explicit

Sub A_Filter_Copy()

Dim CriteriaU As Double
Dim CriteriaL As Double

CriteriaU = ActiveSheet.Cells(1, 8) 'H1
CriteriaL = ActiveSheet.Cells(2, 8) 'H2
  
 'MsgBox CriteriaU & " " & CriteriaL

Application.ScreenUpdating = False

With ActiveSheet
    .Range("F1", .Range("F1").End(xlDown)).AutoFilter Field:=1, _
        Criteria1:=">=" & CriteriaU, Operator:=xlAnd, Criteria2:="<=" & CriteriaL

    .AutoFilter.Range.Copy
    .Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
     Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
    
    .AutoFilterMode = False
End With

Application.ScreenUpdating = False
Application.CutCopyMode = False

End Sub