Hving a Macro meltdown, help

[bionicle]

Hi All,

having issues in getting a macro to work the way I want it to.

my spread sheet has columns A-CO being used and rows 1-199. Row 1 is the column headings.

I have a macro that sorts column K alphabetically whoever in doing so it has reversed it so instead of starting at "A****" in column K it starts with "W****"

I have tried to swap the descending and ascending order around which works and the Column does in deed then starts at "A****" but it also starts in row 70 for some unknown reason.

any help would be gratefully received.

my macro is as follows:

Sub Macro1()
Range("A2:co199").Select
Selection.Sort Key1:=Range("k2:k199"), Order1:=xlDescending, Key2:=Range("k2:k199") _
, Order2:=xlAescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
End Sub

 

[JackDanIce]

I think the issue is with your header argument, try:

Sub Macro1()
Application.ScreenUpdating = False
With Range("A1:CO199")
    .Sort Key1:=Range("K1:K199"), Order1:=xlAescending, Key2:=Range("K1:K199"), Order2:=xlAescending, _
        header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
Application.ScreenUpdating = True
End Sub

 

[bionicle]

I think the issue is with your header argument, try:

Sub Macro1()
Application.ScreenUpdating = False
With Range("A1:CO199")
    .Sort Key1:=Range("K1:K199"), Order1:=xlAescending, Key2:=Range("K1:K199"), Order2:=xlAescending, _
        header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
Application.ScreenUpdating = True
End Sub

Tried the above but it returns a 'Run-time error '1004'
Sorted method of range class failed

 

[JackDanIce]

Try:

Sub Macro1()
Application.ScreenUpdating = False
With Range("A1:CO199")
    .Sort Key1:=Range("K1"), Order1:=xlAescending, Key2:=Range("K1"), Order2:=xlAescending, _
        header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
Application.ScreenUpdating = True
End Sub

 

[bionicle]

Forgot to mention that there are formulas in the whole of column k so I'm not sure if the macro is looking at these thus this is the issue.

 

[Peter_SSs]

You have a couple of things wrong in your code.

1. xlAescending is not a valid term. It should be xlAscending

2. You have two sort keys, both on column K, one is ascending and one is descending. It isn't much wonder the code is confused.

Also, selecting the range first is not necessary and slows your code.

Try

Sub Macro1()
  Range("A2:co199").Sort Key1:=Range("k2:k199"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

 

[bionicle]

You have a couple of things wrong in your code.

1. xlAescending is not a valid term. It should be xlAscending

2. You have two sort keys, both on column K, one is ascending and one is descending. It isn't much wonder the code is confused.

Also, selecting the range first is not necessary and slows your code.

Try

Sub Macro1()
  Range("A2:co199").Sort Key1:=Range("k2:k199"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

thanks for the reply, your code works well however its still starting on row 69 rather than row 2, is this because the macro is seeing the formula that pulls the data from another sheet thus seeing the = as the correct place to start the list.

if this is the case can code be added to ignore this?

 

[Peter_SSs]

1. Could you please post the formula that is in cell K2 and the formula that is in cell K69?

2. What is the name of the worksheet that this data being sorted is on?

 

[bionicle]

1. Could you please post the formula that is in cell K2 and the formula that is in cell K69?

2. What is the name of the worksheet that this data being sorted is on?

its a nice a simple one, starts at F17 and is dragged down to F170

='Labour Load'!F17

and is consecutive to F170

 

[Peter_SSs]

We are sorting on column K starting at row 2 & finishing at row 199, at least that is what you told us in post No. 1. I asked for the formula from 2 specific cells in column K. You gave me one formula from a completely different column. You are also now referring to rows 17 to 170. All very confusing!

I asked for the name of the worksheet the data to be sorted is on. You didn't give it to me.