#1
|
|||
|
|||
filter sheet
Hi,
I have some data in sheet 1 and I want to filter them using an userform. column A: Name column B: Num1 column C: Num2 in my user form I have a combobox to select Name, 2 textboxes to enter min and max for Num1, and 2 other textboxes to enter min and max for Num2. after I Enter the data the following code will be run by clicking on Filter Button Code:
Private Sub CommandButton1_Click() Dim EOR If TextBox2.Text <> Empty And TextBox1.Text > TextBox2.Text Then MsgBox "Wrong Numbers" Exit Sub End If If TextBox4.Text <> Empty And TextBox3.Text > TextBox4.Text Then MsgBox "Wrong Numbers" Exit Sub End If Sheet1.AutoFilterMode = False Sheet1.Range("A2:I1").AutoFilter EOR = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row Sheet1.AutoFilterMode = False Sheet1.Range("A2:C" & EOR).AutoFilter With Sheet1.Range("A3:C" & EOR) If ComboBox1.Text <> Empty Then .AutoFilter Field:=1, Criteria1:="=" & ComboBox1.Text If TextBox1.Text <> Empty Then .AutoFilter Field:=2, Criteria1:=">=" & TextBox1.Value If TextBox2.Text <> Empty Then .AutoFilter Field:=2, Criteria1:="<=" & TextBox2.Value If TextBox3.Text <> Empty Then .AutoFilter Field:=3, Criteria1:=">=" & TextBox3.Value If TextBox4.Text <> Empty Then .AutoFilter Field:=3, Criteria1:="<=" & TextBox4.Value End With End Sub please help me the combobox and textboxes can be empty. if it is better to use Advanced filter, Please help me with that. |
#2
|
|||
|
|||
Hi
In order to filter on 2 criterias you will have to submit Criteria1 and Criteria2. The part for filtering has to look like this: Code:
. . . With Sheet1.Range("A3:C" & EOR) If ComboBox1.Text <> Empty Then .AutoFilter Field:=1, Criteria1:="=" & ComboBox1.Text Else Exit Sub End If If TextBox1.Text <> "" Then If TextBox2.Text <> "" Then .AutoFilter Field:=2, Criteria1:=">=" & TextBox1.Value, Criteria2:="<=" & TextBox2.Value Else .AutoFilter Field:=2, Criteria1:=">=" & TextBox1.Value End If Else If TextBox2.Text <> "" Then .AutoFilter Field:=2, Criteria1:=">=" & TextBox2.Value End If End If 'If TextBox3.Text <> Empty Then .AutoFilter Field:=3, Criteria1:=">=" & TextBox3.Value 'If TextBox4.Text <> Empty Then .AutoFilter Field:=3, Criteria2:="<=" & TextBox4.Value End With End Sub Cheers |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Create a New Sheet from Existing Sheet with Specific Columns | malam | Excel Programming | 1 | 10-17-2014 10:01 PM |
Is it possible to take a row on sheet 1 and transfer the info to sheet 2 by rows on conditions. | rogcar75 | Excel | 3 | 08-28-2014 05:21 PM |
Help Coloring a cell in Sheet one if data is missing from another sheet | Aeducan | Excel | 1 | 06-22-2014 04:49 PM |
From an XL sheet ,how to keep the group of columns which match with other XL sheet | Zubairkhan | Excel | 2 | 03-04-2014 10:57 PM |
Construct a summary sheet by summing up from one or more than one sheet. | PRADEEPB270 | Excel | 1 | 11-04-2011 03:46 AM |