macro to sort is missing top row out???
 

Hi guys,
been running macros (activated by buttons) to sort spreadsheet from
specific collumns e.g.

ActiveSheet.Unprotect
Range("B10:GJ89").Select
Selection.Sort Key1:=Range("CO10"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("CO1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True
ActiveWindow.ScrollColumn = 91
End Sub


Now, having edited this worksheet, when I run each macro: it misses out the
top row e.g. row #10 but sorts the rest??????
Checked the macro and it hasn`t changed and this is happening to all my
macros now on this sheet. Use the same macros on similar sheets in workbook
and they still work fine. Sorted the sheet `by hand` and it sorts just fine.
Any ideas : I`m stuck until I get it sorted an on a deadline HELP!
Thanks
RR1

macro to sort is missing top row out???
 

Change
Header:=xlGuess
to
Header:=xlNo

You're letting Excel figure out if your data has a heading row (apparently
it doesn't, or you wouldn't want row 10 sorted). xlNo tells Excel that the
dta does not include a heading row.

Hope this helps,

Hutch

"Romileyrunner1" wrote:

Hi guys,
been running macros (activated by buttons) to sort spreadsheet from
specific collumns e.g.

ActiveSheet.Unprotect
Range("B10:GJ89").Select
Selection.Sort Key1:=Range("CO10"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("CO1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True
ActiveWindow.ScrollColumn = 91
End Sub


Now, having edited this worksheet, when I run each macro: it misses out the
top row e.g. row #10 but sorts the rest??????
Checked the macro and it hasn`t changed and this is happening to all my
macros now on this sheet. Use the same macros on similar sheets in workbook
and they still work fine. Sorted the sheet `by hand` and it sorts just fine.
Any ideas : I`m stuck until I get it sorted an on a deadline HELP!
Thanks
RR1

macro to sort is missing top row out???
 

Hi

In you sort statement, the Header property is set to xlGuess, and as
you want to sort all rows (no header) it seems that excel guess is
wrong.

Try to change to:

Header:= xlNo

Regards,
Per

On 28 Sep., 21:27, Romileyrunner1
wrote:
Hi guys,
been *running macros (activated by buttons) to sort spreadsheet from
specific collumns e.g.

ActiveSheet.Unprotect
* * Range("B10:GJ89").Select
* * Selection.Sort Key1:=Range("CO10"), Order1:=xlAscending,
Header:=xlGuess, _
* * * * OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
* * * * DataOption1:=xlSortNormal
* * Range("CO1").Select
* * ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
* * * * , AllowSorting:=True
* *ActiveWindow.ScrollColumn = 91
End Sub

Now, having edited this worksheet, when I run each macro: it misses out the
top row e.g. row #10 but sorts the rest??????
Checked the macro and it hasn`t changed and this is happening to all my
macros now on this sheet. Use the same macros on similar sheets in workbook
and they still work fine. Sorted the sheet `by hand` and it sorts just fine.
Any ideas : I`m stuck until I get it sorted an on a deadline HELP!
Thanks
RR1

macro to sort is missing top row out???
 

Yes, yes yes!!!
Well done that man.
Works fine now:
Brilliant work per
Thanks.

"Per Jessen" wrote:

Hi

In you sort statement, the Header property is set to xlGuess, and as
you want to sort all rows (no header) it seems that excel guess is
wrong.

Try to change to:

Header:= xlNo

Regards,
Per

On 28 Sep., 21:27, Romileyrunner1
wrote:
Hi guys,
been running macros (activated by buttons) to sort spreadsheet from
specific collumns e.g.

ActiveSheet.Unprotect
Range("B10:GJ89").Select
Selection.Sort Key1:=Range("CO10"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("CO1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True
ActiveWindow.ScrollColumn = 91
End Sub

Now, having edited this worksheet, when I run each macro: it misses out the
top row e.g. row #10 but sorts the rest??????
Checked the macro and it hasn`t changed and this is happening to all my
macros now on this sheet. Use the same macros on similar sheets in workbook
and they still work fine. Sorted the sheet `by hand` and it sorts just fine.
Any ideas : I`m stuck until I get it sorted an on a deadline HELP!
Thanks
RR1

macro to sort is missing top row out???
 

Son of a gun Tom, well done you.
Works just as it should now.
Many thanks for your quick response.
Thanks Tom.
RR1

"Tom Hutchins" wrote:

Change
Header:=xlGuess
to
Header:=xlNo

You're letting Excel figure out if your data has a heading row (apparently
it doesn't, or you wouldn't want row 10 sorted). xlNo tells Excel that the
dta does not include a heading row.

Hope this helps,

Hutch

"Romileyrunner1" wrote:

Hi guys,
been running macros (activated by buttons) to sort spreadsheet from
specific collumns e.g.

ActiveSheet.Unprotect
Range("B10:GJ89").Select
Selection.Sort Key1:=Range("CO10"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("CO1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True
ActiveWindow.ScrollColumn = 91
End Sub


Now, having edited this worksheet, when I run each macro: it misses out the
top row e.g. row #10 but sorts the rest??????
Checked the macro and it hasn`t changed and this is happening to all my
macros now on this sheet. Use the same macros on similar sheets in workbook
and they still work fine. Sorted the sheet `by hand` and it sorts just fine.
Any ideas : I`m stuck until I get it sorted an on a deadline HELP!
Thanks
RR1