Hiding rows with VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

blommerse@saz.nl - 05 Mar 2007 10:32 GMT

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

Reply to this Message

Bob Phillips - 05 Mar 2007 11:36 GMT

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

Signature

---
HTH

Bob

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

> Hi all,
>
[quoted text clipped - 43 lines]
> Regards,
> BL

Reply to this Message

Bob Phillips - 05 Mar 2007 11:36 GMT

Signature

---
HTH

Bob

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

> Hi all,
>
[quoted text clipped - 43 lines]
> Regards,
> BL

Reply to this Message

blommerse@saz.nl - 05 Mar 2007 12:16 GMT

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??

Reply to this Message

Bob Phillips - 05 Mar 2007 13:35 GMT

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.

Signature

---
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!
[quoted text clipped - 43 lines]
>
> Is this the right way??

Reply to this Message

blommerse@saz.nl - 05 Mar 2007 14:10 GMT

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

Reply to this Message

Bob Phillips - 05 Mar 2007 17:05 GMT

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.

Signature

---
HTH

Bob

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

> OK,
>
[quoted text clipped - 16 lines]
> Please can you help me again??
> Thanks

Reply to this Message

blommerse@saz.nl - 06 Mar 2007 09:28 GMT

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

Reply to this Message

Bob Phillips - 06 Mar 2007 10:04 GMT

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.

Signature

---
HTH

Bob

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

> Morning Bob,
>
[quoted text clipped - 6 lines]
>
> Thanks

Reply to this Message

blommerse@saz.nl - 06 Mar 2007 10:40 GMT

Thank you very, very much Bob for all your work.
Regards,

B. Lommerse

Reply to this Message