Conditional Hide/Unhide Rows

Hi All,

I have the following spreadsheet:

I have a cell (e.g. B11) in which I have a drop-down menu with the numbers 1-10.
Below this cell I have 10 blocks of rows. Each block contains a number of rows (say 7). E.g. rows 12-18; rows 19-25; rows 26-32; rows 33-39 etc. All these rows are currently hidden.

Now I want the following to happen: After selecting a number (say 4) from this dropdown menu I want to the first 4 blocks of rows (e.g. rows 12-39) to appear. When I would select 2 from the dropdown menu, I want the first 2 blocks of rows to appear (e.g. row 12-25). Etc.

Would this be possible without Macro (through some sort of conditional formatting?)? If I would need a macro, could anyone help me with this?

Thanks for your help!

Eduard

Thanks, I have inserted the macro, but can't seem to get it working. What am I doing wrong? See attachedTest.xlsm

Thanks,

Eduard

Thanks, but still not working. Feel very stupid... See upload.

If, I want to show text instead of a 0 when all rows are hidden, how should I change the Macro?

Eduard

Thanks! It works now... When all rows are hidden, cell B11 will show the text "Maak je keuze...". This messes up the macro. Any solution?

Thanks, this works. But when I select a number and go back to the text"("Maak je keuze..."), it returns error 13. When tracking the error, it says the error is in "Range(Cells(12, 1), Cells(13 + i * cRows, 1)).EntireRow.Hidden = False". Any idea what could cause the error?

If I want to add multiple copies of the same macro (different ranges, same worksheet), how should I change the name "Private Sub Worksheet_Change(ByVal Target As Range)"?

Strange, something went wrong. I also posted a reply thanking you for your help and saying everything works smoothly. I'll try again: Thanks!

But if I want to add multiples versions of this macro to the same worksheet, how should I change the name "Private Sub Worksheet_Change(ByVal Target As Range)"? Just adding a number returns an error.

Thanks in advance for final piece of my puzzle!

Thanks, but where to start the macro? The "i" and cell B11 isn't the same for all macros?

Hi,

How do I alter the macro above that instead of numbers, it works on written text (I have a drop down menu with 4 options).

For example, if you type "Optie 1 Verzoek" it hides rows 12-50; if you type "Andere mogelijkheid" it hides rows 52-100; if you type "Dit kan ook" it hides rows 12-100, and finally if you type "Of dit" it hides rows 12-150.

Thanks for the help!

FYI: I'm working with the following macro from the text above:

'macro number 1 - Type Aanvraag
If Not Intersect(Target, gRange) Is Nothing Then
Application.ScreenUpdating = False
If aRange = "Kies#:" Then
Application.EnableEvents = False
Range(Cells(11, 1), Cells(10 + k * 10, 1)).EntireRow.Hidden = True
Application.EnableEvents = 1
Exit Sub
Else
Range(Cells(12, 1), Cells(11 + k * 10, 1)).EntireRow.Hidden = True
Range(Cells(12, 1), Cells(11 + k * cRows, 1)).EntireRow.Hidden = False
End If

Anyone that can help me on the question above? Any help would be greatly appreciated!
Thanks

*Bump no response*

My apologies, forgot to use the brackets. Here is a second try.

Hi,

How do I alter the macro above that instead of numbers, it works on written text (I have a drop down menu with 4 options).

For example, if you type "Optie 1 Verzoek" it hides rows 12-50; if you type "Andere mogelijkheid" it hides rows 52-100; if you type "Dit kan ook" it hides rows 12-100, and finally if you type "Of dit" it hides rows 12-150.

Thanks for the help!

FYI: I'm working with the following macro from the text above:

Please Login or Register  to view this content.