Hiding rows with VBA

  • Thread starter Thread starter blommerse
  • Start date Start date
B

blommerse

Hi all,

In my sheet called "insertsheet I have this VBA:
(thanks to some users of Google groups)

Private Sub Worksheet_Change(ByVal Target As Range)
Const nMAX As Long = 10
Dim nRows As Long

Application.ScreenUpdating = False
With Me
With .Range("B8")
If Intersect(Target(1), .Cells) Is Nothing Then Exit
Sub
nRows = .Value
End With
.Range(.Cells(1, 5), .Cells(1, _
.Columns.Count)).EntireColumn.Hidden = True
.Range(Cells(1, 5), .Cells(1, _
nRows * 2 + 5)).EntireColumn.Hidden = False
End With
With Sheets("Dataprocessing").Range("23:23")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
With Sheets("List & Media").Range("25:25")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
With Sheets("Briefing").Range("31:31")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
End Sub

Everytime B8 is changing some rows and colums are hiding.
Now I want to hide some rows in sheet called "Dataprocessing"
39 & 56 when B8 in "insertsheet is 2
41 & 56 when B8 in insertsheet is 3...
etcetera

Who can help me out of this??

THANKS!

Regards,
BL
 
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
If Target.Address <> "$B$8" Then Exit Sub
With Sheets("Dataprocessing")
Select Case Target.Value
Case 1:
.Rows(39).Hidden = True
.Rows(56).Hidden = True
Case 1:
.Rows(41).Hidden = True
.Rows(56).Hidden = True
'etc
End Select
End With
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
If Target.Address <> "$B$8" Then Exit Sub
With Sheets("Dataprocessing")
Select Case Target.Value
Case 1:
.Rows(39).Hidden = True
.Rows(56).Hidden = True
Case 1:
.Rows(41).Hidden = True
.Rows(56).Hidden = True
'etc
End Select
End With
Application.ScreenUpdating = True
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks Bob for your reply.
Now I get something like this!
Private Sub Worksheet_Change(ByVal Target As Range)
Const nMAX As Long = 10
Dim nRows As Long

Application.ScreenUpdating = False
With Me
With .Range("B8")
If Intersect(Target(1), .Cells) Is Nothing Then Exit
Sub
nRows = .Value
End With
.Range(.Cells(1, 5), .Cells(1, _
.Columns.Count)).EntireColumn.Hidden = True
.Range(Cells(1, 5), .Cells(1, _
nRows * 2 + 5)).EntireColumn.Hidden = False
End With
With Sheets("Dataprocessing").Range("23:23")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
With Sheets("List & Media").Range("25:25")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
With Sheets("Briefing").Range("31:31")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With

Application.ScreenUpdating = False
If Target.Address < "Insertsheet" > "$B$8" Then Exit Sub
With Sheets("Dataprocessing")
Select Case Target.Value
Case 1:
.Rows(39).Hidden = True
.Rows(56).Hidden = True
Case 1:
.Rows(41).Hidden = True
.Rows(56).Hidden = True
End Select
End With
Application.ScreenUpdating = True
End Sub

Is this the right way??
 
No, not really.

I see you are still using the old code to hide rows on Dataprocessing. I had
assumed that was now redundant.

Also, this line

If Target.Address < "Insertsheet" > "$B$8" Then Exit Sub

is syntactically incorrect. Again, I assumed this code was bhind the
Insertsheet worksheet, so no need to specify that. All that is needed is

If Target.Address <> "$B$8" Then Exit Sub

allowing for the previous point of course.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
OK,

Now I try to do this VBA:

Application.ScreenUpdating = False
If Target.Address <> "$B$8" Then Exit Sub
With Sheets("Dataprocessing")
Select Case Target.Value
Case 1:
.Rows("39:56").Hidden = True

In cell B8 of my insertsheet you can fill in 1 till 9.
So when B8 is 1, row 37:38 are visible, rows till row 56 hidden
When B8 is 2, row 37: 40 are visible, rows till row 56 hidden
When B8 is 3, row 37:42 are visible, rows till row 56 hidden.

I think I didn't give you the right/anough info.

Please can you help me again??
Thanks
 
Application.ScreenUpdating = False
If Target.Address <> "$B$8" Then Exit Sub
With Sheets("Dataprocessing")
Select Case Target.Value
Case 1: .Rows("39:56").Hidden = True
Case 2: .Rows("41:56").Hidden = True
Case 3: .Rows("43:56").Hidden = True
'etc.



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Morning Bob,

Me again...
It almost work perfect.
The Rows have to resize when B8 is another number.
Now when I put 1 in B8 it works, but when I put 2 in B8, nothing
happened.
Can u help me for the last time (I hope) :)

Thanks
 
Maybe you need to unhide themn all first in case some hidden from last time

Application.ScreenUpdating = False
If Target.Address <> "$B$8" Then Exit Sub
With Sheets("Dataprocessing")
.Rows("1:" & .Rows.Count).Hidden = False
Select Case Target.Value
Case 1: .Rows("39:56").Hidden = True
Case 2: .Rows("41:56").Hidden = True
Case 3: .Rows("43:56").Hidden = True
'etc.



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top