HIDE row if CELL value is #NA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BKO - 11 Jun 2007 12:35 GMT

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

Signature

There are only 10 types of people in the world:
Those who understand binary and those who don''t.

Reply to this Message

Mike H - 11 Jun 2007 13:05 GMT

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

> 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

Reply to this Message

BKO - 11 Jun 2007 13:33 GMT

Thanks very much mike, Your function works fine,

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

Signature

There are only 10 types of people in the world:
Those who understand binary and those who don''t.

> Try:-
>
[quoted text clipped - 15 lines]
> >
> > Thank you very much in advance

Reply to this Message

Mike H - 11 Jun 2007 13:39 GMT

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

> Thanks very much mike, Your function works fine,
>
[quoted text clipped - 19 lines]
> > >
> > > Thank you very much in advance

Reply to this Message

krcowen@aol.com - 11 Jun 2007 20:48 GMT

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

> From the forms toolbar put a button on the worksheet you want this to work on
> and assign the code to it.
[quoted text clipped - 39 lines]
>
> - Show quoted text -

Reply to this Message