Hi,

I am trying to hide rows depending on what is selected from a list. This is the first time I have used VB, but I have found/adapted this from examples found online.

The 220, 230, 240 & 250 numbers are products in a list. See VBA code below:-


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("B19")) Is Nothing Then
With Sheet2
.Rows("44").EntireRow.Hidden = Target.Value = "220"
.Rows("47").EntireRow.Hidden = Target.Value = "220"
.Rows("43").EntireRow.Hidden = Target.Value = "230"
.Rows("46").EntireRow.Hidden = Target.Value = "230"
.Rows("40:48").EntireRow.Hidden = Target.Value = "240"
End With
End If
End Sub

The code works fine until I add the 'Rows("40:48")...' line. With this line added, when 240 is selected it removes rows 40-48, but it stops 220 & 230 from working. I have gone step by step through the code and it appears that when row 44 is selected, it causes it not to work.

For example if I modify the line to read 'Rows("40:43")...' it works fine, also 45:48 works fine.

Anyone have any ideas?


Thanks,

Dominic