Gidday
06-07-2010, 05:01 AM
Hi All
The following piece of code was kindly supplied to me by xld and it works really well. I have now stumbled across a little hurdle. I have a simple macro that inserts a row in my worksheet. When this happens the rows this code targets (Rows("23:30") move down and out of range and the more rows I insert the more out of range they go. So rather than defining the range implicitly is there a another way to target the range.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A4" '<== change to suit
Dim CI As Long
On Error Goto ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Rows("23:30").Hidden = .Value2 = "Quote"
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Thanks
Joe
The following piece of code was kindly supplied to me by xld and it works really well. I have now stumbled across a little hurdle. I have a simple macro that inserts a row in my worksheet. When this happens the rows this code targets (Rows("23:30") move down and out of range and the more rows I insert the more out of range they go. So rather than defining the range implicitly is there a another way to target the range.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A4" '<== change to suit
Dim CI As Long
On Error Goto ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Rows("23:30").Hidden = .Value2 = "Quote"
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Thanks
Joe