Help needed with multi-sheet routine

[David]

Hi all,

I've been hacking around for a few hours now

I'm trying to clear constants from target rows on 2 sheets when I select
both sheets, click on a name and hit Delete

The following works:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("a3:a" & ActiveSheet.UsedRange.Rows.Count -
2)) Is Nothing Then Exit Sub
If ActiveWindow.SelectedSheets.Count > 1 Then
If Target = "" Then
Target.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
Sheets(4).Range(Target.Address).EntireRow.SpecialCells
(xlCellTypeConstants).ClearContents
End If
Range("SortRange").Sort key1:=Range("A3"), header:=xlNo
Sheets(4).Range("SortRange").Sort key1:=Sheets(4).Range("A3"), header:
=xlNo
Range("A2").Select
Sheets(2).Select
End If
End Sub

I tried to streamline things with this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
On Error Resume Next
If Intersect(Target, Range("a3:a" & ActiveSheet.UsedRange.Rows.Count -
2)) Is Nothing Then Exit Sub
For Each sh In ActiveWindow.SelectedSheets
If Target = "" Then
sh.Range(Target.Address).EntireRow.SpecialCells
(xlCellTypeConstants).ClearContents
End If
sh.Range("SortRange").Sort key1:=sh.Range("A3"), header:=xlNo
sh.Range("A2").Select
Next
Sheets(2).Select
End Sub

But this line doesn't clear the entire row on the second sheet, only the
name I clicked on:

sh.Range(Target.Address).EntireRow.SpecialCells
(xlCellTypeConstants).ClearContents

Can anyone help?

 

[Gary Keramidas]

this is a personal preference, but, if you're not an employee of microsoft, i
really wish you didn't use @microsoft.com in your email address. use
(e-mail address removed).

 

[David]

Gary Keramidas wrote

this is a personal preference, but, if you're not an employee of
microsoft, i really wish you didn't use @microsoft.com in your email
address. use (e-mail address removed).

Huh?

What I'm using:

I don't see how that translates to @microsoft.com

 

[Gary Keramidas]

sorry, got posted to the wrong david, should have been posted to a post by
dave marden.

 

[Tom Ogilvy]

If it clears the Target cell address on each sheet, then you have a problem
beyond this, because your code isn't supposed to execute unless that cell is
already clear. Anyway, try this modification

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
Dim Target1 as Range
On Error Resume Next
If Intersect(Target, Range("a3:a" & ActiveSheet _
.UsedRange.Rows.Count - 2)) Is Nothing Then Exit Sub
For Each sh In ActiveWindow.SelectedSheets
set Target1 = sh.Range(Target.Address)
If Target1 = "" Then
Target1.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
End If

sh.Range("SortRange").Sort key1:=sh.Range("A3"), header:=xlNo
'sh.Range("A2").Select ' can't select on a sheet that isn't active

Next
Sheets(2).Select
End Sub

 

[David]

Tom Ogilvy wrote

Anyway, try this modification

For Each sh In ActiveWindow.SelectedSheets
set Target1 = sh.Range(Target.Address)
If Target1 = "" Then
Target1.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
End If

Thanks, your modification works fine. Althogh the number of lines isn't
reduced from my working routine, it at least looks cleaner, and will work
unmodified if I move sheets around in the future

'sh.Range("A2").Select ' can't select on a sheet that isn't active

Hmm. For me, it does.

 

[Tom Ogilvy]

'sh.Range("A2").Select ' can't select on a sheet that isn't active

Hmm. For me, it does.

Try this is in a new workbook without your error handler hiding all your
misadventures

Sub ABC()
Worksheets(Array("Sheet2", "Sheet3")).Select
Worksheets("Sheet2").Activate
Worksheets("Sheet3").Range("A2").Select
End Sub

Because you have your sheets grouped, when you select A2 on the activesheet
(when sh is the Activesheet), it is selected on all the grouped sheets. So
to you it looks like it works I suspect.

alternately, remove your crutch
On Error Resume Next

and see what happens.

 

[David]

Tom Ogilvy wrote

Try this is in a new workbook without your error handler hiding all
your misadventures

Sub ABC()
Worksheets(Array("Sheet2", "Sheet3")).Select
Worksheets("Sheet2").Activate
Worksheets("Sheet3").Range("A2").Select
End Sub

Because you have your sheets grouped, when you select A2 on the
activesheet (when sh is the Activesheet), it is selected on all the
grouped sheets. So to you it looks like it works I suspect.

alternately, remove your crutch
On Error Resume Next

and see what happens.

Point taken. This works with the crutch:
Application.Goto sh.Range("A2"), True

That said, I like crutches, and I put it in primarily to eliminate the
error if there were no Constants to the right in the row.

 

[Tom Ogilvy]

That said, I like crutches, and I put it in primarily to eliminate the

error if there were no Constants to the right in the row.

As a technique, Most use something similar to:

On Error Resume Next
Target.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
On Error goto 0

But it is your routine.