Sorting issue

  • Thread starter Thread starter Sige
  • Start date Start date
S

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
 
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
 
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
 
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
 
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
Click to expand...
 
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:P").Delete
End With
Next wks
End Sub
 
Hi Sige,

You cannot select a range on an inactive sheet. So, perhaps, replace:
DelRng.Select
Click to expand...

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.
 
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
 
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
Click to expand...
Returns a "1004":reference not valid.

Any reason why? Just for my understanding...

Best Regards, Sige
 
Hi Sige,
But
Returns a "1004":reference not valid.

Any reason why? Just for my understanding...
Click to expand...


The Select portion is just a typo. Try:

Application.Goto delRng
 
Back
Top