Sorting issue

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 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"),


---
Regards,
Norman



"Sige" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1139492607.652446.304150@o13g2000cwo.googlegroups.com...
> 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

---
Regards,
Norman



"Sige" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1139494345.397141.99050@o13g2000cwo.googlegroups.com...
> 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
>

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.

Sige wrote:
>
> 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

Thanks a lot Dave!

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

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.

---
Regards,
Norman



"Sige" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1139501126.749460.283690@o13g2000cwo.googlegroups.com...
> 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
>

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 wrote:
>
> 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

--

Dave Peterson

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


Dave Peterson wrote:
> 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 wrote:
> >
> > 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
>
> --
>
> Dave Peterson

Hi Sige,

> But
>> application.goto DelRng.Select
> Returns a "1004":reference not valid.
>
> Any reason why? Just for my understanding...


The Select portion is just a typo. Try:

Application.Goto delRng


---
Regards,
Norman

That makes sense!

Thank you,

Sige

Thanks, Norman.

Stupid fingers (and copy|pasting!)

Norman Jones wrote:
>
> Hi Sige,
>
> > But
> >> application.goto DelRng.Select
> > Returns a "1004":reference not valid.
> >
> > Any reason why? Just for my understanding...
>
> The Select portion is just a typo. Try:
>
> Application.Goto delRng
>
> ---
> Regards,
> Norman

--

Dave Peterson