Sorting issue

[Sige]

Hi There,

Underneath sub bugs on:

ws.Range("B6").Sort Key1:=Range("B6"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Any ideas?

Best Regards, Sige


Sub MakeNAMS()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

ws.Range("B6").Sort Key1:=Range("B6"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Rows("7:7").Delete Shift:=xlUp
ws.Range("B7").Subtotal GroupBy:=2, Function:=xlSum,
TotalList:=Array(3, 4, 5, 6, _
7, 8, 9, 10, 11, 12, 13, 14, 15), Replace:=False,
PageBreaks:=False, _
SummaryBelowData:=True
ActiveWindow.DisplayOutline = True

Next ws
End Sub

 

[Norman Jones]

Hi Sige,

You need to qualify the sort key range: change

ws.Range("B6").Sort Key1:=Range("B6"),

to

ws.Range("B6").Sort Key1:=ws.Range("B6"),

 

[Sige]

Hi Norman,

I am getting close ... ;o)

One more thing:
How can I show the outlines on all my sheets?

ActiveWindow.DisplayOutline = True shows the outlines only for the
first sheet.

Brgds, Sige

 

[Norman Jones]

Hi Sige,

Since the DisplayOutline property relates to the window object, I think that
it would be necessary to activate the required sheets, e.g.:

Dim SH As Worksheet
Dim rng As Range

Set rng = Selection

For Each SH In ActiveWorkbook.Worksheets
SH.Activate
ActiveWindow.DisplayOutline = True
Next SH

Application.Goto rng

 

[Sige]

Perfect Norman!

Could you please tell why method "range"of "worksheet" fails here ...?

Sub CheckEmptyRows()
Dim ws As Worksheet
Dim myRow As Long

For Each ws In ActiveWorkbook.Worksheets
myRow = ws.Range("A65536").End(xlUp).Row
ws.Range(Cells(7, 16), Cells(myRow, 16)).FormulaR1C1 = _
"=SUM(RC[-13]:RC[-1])=0"
Next ws
End Sub

Thanks, Sige

 

[Dave Peterson]

You have unqualified ranges:

ws.Range(Cells(7, 16), Cells(myRow, 16)).FormulaR1C1 = _
"=SUM(RC[-13]:RC[-1])=0"

Those cells refer to the activesheet--not ws.

ws.Range(ws.Cells(7, 16), ws.Cells(myRow, 16)).FormulaR1C1 = _
"=SUM(RC[-13]:RC[-1])=0"

or

with ws
.Range(.Cells(7, 16), .Cells(myRow, 16)).FormulaR1C1 = _
"=SUM(RC[-13]:RC[-1])=0"
end with

Note the dots in front of .range, .cells.

Perfect Norman!

Could you please tell why method "range"of "worksheet" fails here ...?

Sub CheckEmptyRows()
Dim ws As Worksheet
Dim myRow As Long

For Each ws In ActiveWorkbook.Worksheets
myRow = ws.Range("A65536").End(xlUp).Row
ws.Range(Cells(7, 16), Cells(myRow, 16)).FormulaR1C1 = _
"=SUM(RC[-13]:RC[-1])=0"
Next ws
End Sub

Thanks, Sige

 

[Sige]

Thanks a lot Dave!

 

[Sige]

Sorry to harass you all the time guys ...
Slowly but surely I am starting to understand ... it is a matter of
having the dots at the right place ;o)

Dave, the one underneath you'll surely recognise as it is a "violated"
version of one of your recent ones.
What dot is missing again? ;o)
Now it bugs on: ===> DelRng.Select

Brgds, Sige

Sub DeleteEmptyRows2()
Dim rng As Range
Dim wks As Worksheet
Dim myCell As Range
Dim DelRng As Range
' Set wks = Worksheets("Sheet1")

For Each wks In ActiveWorkbook.Worksheets
With wks
Set rng = .Range("P7", .Cells(.Rows.Count, "P").End(xlUp))

For Each myCell In rng.Cells
If myCell.Value = True Then
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(myCell, DelRng)
End If
End If
Next myCell
If DelRng Is Nothing Then
'do nothing
Else
DelRng.Select
'or (after testing!)
DelRng.EntireRow.Delete
End If
..Columns("P").Delete
End With
Next wks
End Sub

 

[Norman Jones]

Hi Sige,

You cannot select a range on an inactive sheet. So, perhaps, replace:

DelRng.Select

with

wks.Activate
delRng.Select

Or, better still, remove the selection.

I would also change:

With wks

to
Set delRng = Nothing
With wks

in case you encountered a sheet without any required values.

 

[Dave Peterson]

You can only select a range on the activeworksheet.

So you can either toss that .select line (and just keep the .delete)

or you can select the worksheet first.

Else
wks.select
DelRng.Select
'or (after testing!)
DelRng.EntireRow.Delete
End If

Or I like just going to that range:

Else
application.goto DelRng.Select
'or (after testing!)
DelRng.EntireRow.Delete
End If

 

[Sige]

Hi Dave, Norman,

Thanks for your advice!

Without the DelRng.Select it works perfect...or for obvious reasons
more efficiently.
Also with wks.Select or wks.Activate ...

But

application.goto DelRng.Select

Returns a "1004":reference not valid.

Any reason why? Just for my understanding...

Best Regards, Sige

 

[Norman Jones]

Hi Sige,

But
Returns a "1004":reference not valid.

Any reason why? Just for my understanding...

The Select portion is just a typo. Try:

Application.Goto delRng

 

[Sige]

That makes sense!

Thank you,

Sige

 

[Dave Peterson]

Thanks, Norman.

Stupid fingers (and copy|pasting!)