VBA hide rows with variable range

[Is_REF]

Hello!
I am very new with VBA, I will really appreciate some help
I have a list of 100 rows and I want to be able to show/hide only the rows that contains values (from row 5 to row "X"). I have an auxiliary cell "AV1" that counts the number of rows that contain values ("X"=N_Rows) (they will always be ordered for the blanks to be at the end). I also have cell K1 that is 0 when the full list is hidden or =100 when the list is shown.

Sub List()

Dim N_Rows As Long
Range("AV1").Value = N_Rows

If Range("K1").Value = 0 Then
Rows("5:" & N_Rows).EntireRow.Hidden = False
Else
Rows("5:" & N_Rows).EntireRow.Hidden = True
End If
End Sub

I am getting "Run-time error '13': Type mismatch" - What I am doing wrong?
Thanks for your help!

 

[DanteAmor]

It must be the other way around

Sub List()


Dim N_Rows As Long

[COLOR=#0000ff]N_Rows = Range("AV1").Value [/COLOR]


If Range("K1").Value = 0 Then
Rows("5:" & N_Rows).EntireRow.Hidden = False
Else
Rows("5:" & N_Rows).EntireRow.Hidden = True
End If
End Sub

Try and tell me

 

[Michael M]

Change this line

Range("AV1").Value = N_Rows

To

N_Rows=Range("AV1").Value

 

[Is_REF]

It must be the other way around

Sub List()


Dim N_Rows As Long

[COLOR=#0000ff]N_Rows = Range("AV1").Value [/COLOR]


If Range("K1").Value = 0 Then
Rows("5:" & N_Rows).EntireRow.Hidden = False
Else
Rows("5:" & N_Rows).EntireRow.Hidden = True
End If
End Sub

Try and tell me

Thanks a lot!

I have tried different things, finally works! :

Sub List()

Dim N_Rows As Integer
N_Rows = Range("AV1").Value + 4

If Range("K1").Value = 0 Then
Rows("5:" & N_Rows).EntireRow.Hidden = False
Else
Rows("5:" & N_Rows).EntireRow.Hidden = True
End If
End Sub

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.