Help needed with multi-sheet routine
 

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.SpecialC ells
(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?

--
David

Help needed with multi-sheet routine
 

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
.



--


Gary


"David" wrote in message
...
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.SpecialC ells
(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?

--
David

Help needed with multi-sheet routine
 

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 .




Huh?

What I'm using:
"David" wrote in message

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

--
David

Help needed with multi-sheet routine
 

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

--


Gary
"David" wrote in message
...
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 .




Huh?

What I'm using:
"David" wrote in message

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

--
David

Help needed with multi-sheet routine
 

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

--
Regards,
Tom Ogilvy


"David" wrote in message
...
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.SpecialC ells
(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?

--
David

Help needed with multi-sheet routine
 

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.

--
David

Help needed with multi-sheet routine
 

'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.

--
Regards,
Tom Ogilvy




"David" wrote in message
...
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.

--
David

Help needed with multi-sheet routine
 

Tom Ogilvy wrote

'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.


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.

--
David

Help needed with multi-sheet routine
 

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.

--
Regards,
Tom Ogilvy

"David" wrote in message
...
Tom Ogilvy wrote

'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.


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.

--
David