Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 17th 20, 02:23 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 37
Default Deleting rows when Worksheet_Change active

Hi Team

The below Worksheet_Change codes work as expected, with one caveat, I get an error if I delete any rows within the Target(Range).

I fully appreciate and understand Error Handling, but I am unsure as to which way to approach this particular issue.

Does anyone have a handy line of Error Handling code I can insert that will suppress the Error popup from displaying If/When rows are deleted please.

As always, TIA
Mark.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C9:C42")) Is Nothing Then
If Target.Value = "YARD" Then
Target.Offset(0, 7).Select
Else
Target.Offset(0, 1).Select
End If
End If

If Not Intersect(Target, Range("H9:H42")) Is Nothing Then
Target.Offset(0, 2).Select
End If

End Sub

  #2   Report Post  
Old November 17th 20, 02:32 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 37
Default Deleting rows when Worksheet_Change active

False alarm.

For those interested in the work around:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C9:C42")) Is Nothing Then
If Target.Cells.Count 1 Then Exit Sub
If Target.Value = "YARD" Then
Target.Offset(0, 7).Select
Else
Target.Offset(0, 1).Select
End If
End If

If Not Intersect(Target, Range("H9:H42")) Is Nothing Then
If Target.Cells.Count 1 Then Exit Sub
Target.Offset(0, 2).Select
End If

End Sub

  #3   Report Post  
Old November 17th 20, 02:40 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,812
Default Deleting rows when Worksheet_Change active

Hi Mark,

Am Mon, 16 Nov 2020 17:32:30 -0800 (PST) schrieb NoodNutt:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C9:C42")) Is Nothing Then
If Target.Cells.Count 1 Then Exit Sub
If Target.Value = "YARD" Then
Target.Offset(0, 7).Select
Else
Target.Offset(0, 1).Select
End If
End If

If Not Intersect(Target, Range("H9:H42")) Is Nothing Then
If Target.Cells.Count 1 Then Exit Sub
Target.Offset(0, 2).Select
End If

End Sub


try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C9:C42,H9:H42")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Select Case Target.Column
Case 3
If Target.Value = "YARD" Then
Target.Offset(, 7).Select
Else
Target.Offset(, 1).Select
End If
Case 8
Target.Offset(, 2).Select
End Select
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #4   Report Post  
Old November 17th 20, 11:10 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 37
Default Deleting rows when Worksheet_Change active

As always Claus you're there, thank you.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
worksheet_change becoming active just by entering the cell kc9r0n Excel Programming 1 March 12th 10 04:23 PM
deleting cell range in a worksheet_change subroutine NDBC Excel Programming 4 September 4th 09 03:13 AM
How to get the correct active Cell position in Worksheet_Change Johan2000 Excel Programming 2 March 6th 08 02:58 AM
Disabling worksheet_change when deleting rows . . Adam[_14_] Excel Programming 3 February 16th 06 05:06 PM
Deleting all rows after the last active cell Shirley Munro[_7_] Excel Programming 2 September 28th 05 08:53 AM


All times are GMT +1. The time now is 09:13 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017