HIDE row if CELL value is #NA

[Guest]

I have a long list where I want to hide the rows when cell B(row) = #NA

I dont want to use Autofilter, is there a function or maco to hide these rows

Thank you very much in advance

 

[Guest]

Try:-

Sub Hiderows()
Dim r As Long, LastRow As Long
LastRow = Range("B65536").End(xlUp).Row
For r = LastRow To 1 Step -1
If IsError(Cells(r, 2)) Then
Rows(r).EntireRow.Hidden = True
End If
Next r
End Sub

Mike

 

[Guest]

Thanks very much mike, Your function works fine,

Can I start the function automatic if the sheet is selected ?

 

[Guest]

From the forms toolbar put a button on the worksheet you want this to work on
and assign the code to it.

You can have a button on more than 1 sheet that calls the macro but it will
always work on the active sheet i.e. the one you were in when it was called.

Mike

 

[krcowen]

If you want it to happen automatically whenever the sheet is activated
you should put it in the worksheet activate event.

Right click on the relevant worksheet, click on view code, and use the
sub name as below:


Private Sub Worksheet_Activate()

Dim r As Long, LastRow As Long
LastRow = Range("B65536").End(xlUp).Row
For r = LastRow To 1 Step -1
If IsError(Cells(r, 2)) Then
Rows(r).EntireRow.Hidden = True
End If
Next r

End Sub

Good luck.

Ken
Norfolk, VA