Hide Rows on cell value.

[adamsm]

Hi,

The following code I'm using filters rows on cell value. How could I make the code to hide the rows instead of filtering?

Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Sheets("Orders").Range("D17").Value) <> "ALL" Then
        Sheets("Orders").Range("C17").AutoFilter Field:=2, Criteria1:=Range("O6").Text
    Else
        If Sheets("Orders").AutoFilterMode Then
            Sheets("Orders").Range("C17").AutoFilter
        End If
    End If
End Sub

Any help on this would be kindly appreciated.

Thanks in advance.

 

[venkat1926]

It is not clears why you wrote the macro as sheet code(event code). It should be in standard module

suppose sample data is like thsi

<table border="0" cellpadding="0" cellspacing="0" width="320"><col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">hdng1</td> <td style="width: 48pt;" width="64">hdng2</td> <td style="width: 48pt;" width="64">hdng3</td> <td style="width: 48pt;" width="64">hdng4</td> <td style="width: 48pt;" width="64">hdng5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>all</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>x</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>x</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>all</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>all</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>y</td> <td>
</td> </tr> </tbody></table>
try this macro (in a sandard module)

Sub test()
Dim j As Integer, k As Integer
Worksheets("orders").Activate
j = Range("D1").End(xlDown).Row
For k = j To 2 Step -1
If Cells(k, "D") <> "all" Then Cells(k, "D").EntireRow.Hidden = True
Next k
End Sub

 

[adamsm]

Thanks for the help. I do really appreciate your help.

 

[adamsm]

How could the code be changed so that it hides rows based on textbox1 value?

For example the user writes "all" in textbox1 and clicks the button. All the rows except the rows containing the text "all" gets hidden.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[venkat1926]

As I said early keep a copy of the original data in the sheet "orders" in sheet 2 also for undoing the macro


I am not very familiar with text box. However I did some experiments.
It looks there is no text box in forms toolbar and you cannot assign the macro to a text box from drawing toolbar. You have to create a text box from control toolbar. after you create this text box
click the design mode (in the vbeditor toolbar)

if you right click and click properties you see the name of the textbox is "textbox1". i kept the name as it is
then again right click the textbox and click view code
in the window that comes up copy this code

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim j As Integer, k As Integer, x As String
Worksheets("orders").Activate
j = Range("D1").End(xlDown).Row
x = TextBox1.Text
'MsgBox x
For k = j To 2 Step -1
'Cells(k, "D").Select
'MsgBox Cells(k, "D") <> x
If Cells(k, "D") <> x Then Cells(k, "D").EntireRow.Hidden = True
Next k

End Sub

now exit design mode.

now doubleclick the text box and see what happens.

perhaps this can be done even if you create a textbox through userform.

 

[adamsm]

Thanks for the help. But sorry for being late to respond.

Anyways, I do really appreciate your help.