Hiding rows with VBA

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
 
B

Bob Phillips

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)
 
B

Bob Phillips

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)
 
B

blommerse

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

Bob Phillips

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)
 
B

blommerse

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
 
B

Bob Phillips

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)
 
B

blommerse

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
 
B

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple sheets should change.. 6
Array macro question 4
Combine three InputBoxes into one? 7
VBA group the number in the Column and Copy to the new worksheet. 0
Who can help me with a VBA 7
Noobee Case Select Question 7
when certain rows are hidden, can I also hide a combobox? 6
Hide Rows in one W/S Based on a cell value in Another W/S 7

Top