VBA Run-time error '1004' when Hide/Show Rows

[jmpjmp]

Hello All,

I am confronted with an error message - "Run-time error '1004': Unable to set the Hidden property of the Range class" when hiding/showing entire rows.

Cells A1:A10 are given values 1 to 10.

I have placed ActiveX ComboBox referencing the above cells as ListFillRange.

The Worksheet is Unprotected.

When I select a number from the ComboBox, lets say 5, then I would like the rows 1-5 to show and 6-10 to hide. Below is the code I have:

Private Sub ComboBox1_Change()

Dim ShowRows As String
Dim HideRows As String

ShowRows = "A1:A" & ComboBox1
HideRows = "A" & ComboBox1 + 1 & ":A10"

Range(ShowRows).EntireRow.Hidden = False
Range(HideRows).EntireRow.Hidden = True

End Sub

The code seems to behave the way I want and hides the rows after the selected number. But it is always presented with the error message.

I have tried the code with row numbers instead of cell range & entire row but the error message persists.

ShowRows = "1:" & ComboBox1
HideRows = ComboBox1 + 1 & ":20"

Rows(ShowRows).Hidden = False
Rows(HideRows).Hidden = Tru

Can someone help identify the problem here?

Cheers,

James

[#2 · Accepted solution]

@jmpjmp 

This is because the ListFillRange is set to A1:A10 and you are trying to hide those rows. An easy fix is to use On Error Resumen Next like below...

On Error Resume Next
Rows(ShowRows).Hidden = False
Rows(HideRows).Hidden = True
On Error GoTo 0

[#3]

@Subodh_Tiwari_sktneer  Thanks for explaining this. And your suggested solution worked well!

[#4]

I managed to produce a formula in excel through the online excel tutorial, covering two of the three conditions. II miss now if the calculation is correct. I have now if the result of the calculation is >40 the result becomes 40, and if the result of the calculation is <0 (negative) the result becomes 0.

the formula I made is as follows:

=IF (180-G6/i6>40,"40",IF(180-G6/I6<0,"0"))

Please Help

[#5]

@Gilbert1240 

You probably want to return a number, not a text value such as "0" or "40".

Try

=MIN(MAX(180-G6/I6, 0), 40)

[#6]

Thank you very much.

[#7]

You're welcome @jmpjmp!