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

--

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

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

"BKO" wrote:

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

--

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

Thanks very much mike, Your function works fine,

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

--

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



"Mike H" wrote:

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

"BKO" wrote:

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

--

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

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

"BKO" wrote:

Thanks very much mike, Your function works fine,

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

--

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



"Mike H" wrote:

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

"BKO" wrote:

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

--

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

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




On Jun 11, 8:39 am, Mike H wrote:
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



"BKO" wrote:
Thanks very much mike, Your function works fine,

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

--

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

"Mike H" wrote:

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

"BKO" wrote:

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

--

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

- Show quoted text -