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.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

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