Filter between values

Yokiboha

Board Regular
Joined
Feb 21, 2007
Messages
207
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

Board Regular
Joined
Feb 21, 2007
Messages
207
Many thanks Howard, it works, but I want to filter the data to another location, is this possible?
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

Code:
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

Board Regular
Joined
Feb 21, 2007
Messages
207

ADVERTISEMENT

Howard, Many thanks it does it!

I am very grateful

Best regards

Yokiboha
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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


Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,545
Members
430,295
Latest member
amdis

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
Top