Hiding Rows based on the value of the InputBox

[KaibleBasha]

Hi,

I am trying to hide all rows where the Surgery name is not equal to the value input into the InputBox, this Inputbox is triggered after the word "WORKER" is input into A1 as this is the correct column label for my report.

I have these two Subs in the same sheets code, which may be why it's not working, but I'm not sure;

Private Sub worksheet_calculate()
Dim LstRw As Long, Rw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Rows
End Sub



Private Sub Worksheet_change(ByVal Target As Range)
Dim Result As String
Application.ScreenUpdating = False
Application.EnableEvents = False
If Range("A1") = "WORKER" Then _
Result = InputBox("Which Surgery?", "Selecting your Surgery", "Surgery name & Address")
For Rw = 2 To LstRw
If Cells(Rw, "F") <> Result Then Cells(Rw, "F").EntireRow.Hidden = True
Next Rw
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


I have tried using this piece;
"Dim LstRw As Long, Rw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Rows"
In the second private sub as well as the rest, however, I don't think this is how it works (Worksheet_Calculate & Worksheet_Change). (Correct me as I am probably wrong).

I am kind of getting a result, however, this is just the screen flashing which may be the rows hiding and unhiding quickly. I have tried putting Range("A1:A" & LstRw).EntireRow.Hidden = False into the first sub, however, this gives the same "flash" effect.

Any ideas where I am going wrong?

Thanks for your help.

Kai


P.s Advice and guidance is welcomed as I'd like to avoid relying on you guys!

 

[hateme28]

Just by looking at the code you have, you need to define LstRw within the same sub you use it. so you might have this line before your if statement

lstRw = cells(rows.count, "F").end(xlup).row

I used column F in the ex. you have col A. is that the correct column you want to define as your last row? (that could also be a mistake)

the piece below should work, but if that is the problem it can be rewritten as

If Cells(Rw, "F") <> Result Then Cells(Rw, "F").EntireRow.Hidden = True
If not cells(rw,"f") = result then cells(Rw, "F").EntireRow.Hidden = True

 

[KaibleBasha]

Hi,

Thanks for responding I am still trying to test this.

Excel keeps becoming unresponsive whenever I try to run it this way.

Just to confirm I have tried defining LstRw in the same sub as the inputbox, however, I am getting an error I think may e related to the worksheet_change. I have also tried with both worksheet_change & calculate.

I've not got a result for the second piece of code yet, as my pc sucks.

Will let you know how I get on.

Thanks
Kai