ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Enabling option „Format rows“ to hide/unhide rows using VBA-code? (https://www.excelbanter.com/excel-discussion-misc-queries/238193-enabling-option-%84format-rows%93-hide-unhide-rows-using-vba-code.html)

ran58

Enabling option „Format rows“ to hide/unhide rows using VBA-code?
 
I have used the code below, which is tied to a multi-drop-down-list
in
order to unhide/hide rows (unhiding rows = yes = 1, hiding rows = no =
2.

The macro works fine, unless the worksheet is being protected. I then
get a runtime-error 1004, saying the hidden property of the range
object cannot be determined (Code-Line 8).
S.o. (Luke M) suggested that I need to have the option of "Format
rows" enabled, or I can't hide/unhide rows. The respective VBA-Code
suggestion was:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingRows:=True

Can s.o. help me integrating the above-mentioned code into below-
mentioned code?
Unfortunately I have only little knowledge in VBA.... Many many
thanks!
Sub HideRows()
Dim Rng As Range
Set Rng = Sheets("Ziel1").Range("K6")
If Rng.Value = 1 Then
Rows("7:19").EntireRow.Hidden = False
Range("K6").Select
ElseIf Rng.Value = 2 Then
Rows("7:19").EntireRow.Hidden = True
End If


All times are GMT +1. The time now is 06:30 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com