code to sort data keeping 2 rows together

jek40

Active Member
Joined
Jan 17, 2005
Messages
317
I found this code that is supposed to be for sorting rows of data keeping 2 rows together.

if i run the code from a button or from tools /macro I get an error message "named arguement not found" on the line DataOption1:=xlSortNormal

can anyone tell me if this code will work for them and how to make it work

Thanks
John


Code:
Sub Macro1()
Dim myRow As Long
Columns("A:B").Insert
Range("A1").Value = "Sort1"
Range("B1").Value = "Sort2"
Range("A2").FormulaR1C1 = "=RC[2]"
Range("A3").FormulaR1C1 = "=R[-1]C[2]"
Range("A2:A3").Copy
myRow = Cells(Rows.Count, 3).End(xlUp).Row
If myRow Mod 2 <> 1 Then myRow = myRow + 1
Range("A2:B" & myRow).Select
ActiveSheet.Paste
With Columns("A:B")
    .Copy
    .PasteSpecial Paste:=xlPasteValues
End With
Range("A3").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, _
    Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Columns("A:B").Delete
End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hello jek40
Could you please give an example of what you're wanting to achieve. I'm not sure what you mean by sorting and keeping 2 rows togther .... thx
 
Upvote 0
for example

row 4 may have data --- date time item info
row 5 may have data ---- quanity sold color

there is too much data to fit in one row and print out

could have many rows of data like this

I would like to sort by date (for example)and have the related 2nd row of data stay with the first

I hope that helps explain it

John
 
Upvote 0
Assumptions:
- Sheet is active
- Dates and Qty are in Column A
- Last two columns of sheet are empty


Code:
Public Sub SpecialSort()
On Error GoTo TheEnd
    Application.ScreenUpdating = False
    Col = 3
    
    'CREATE COLUMNS TO SORT BY
    Columns("A:B").Insert Shift:=xlToRight
    
    'FILL SPECIAL SORT COLUMNS
    Cells(1, 1).Value = 1
    Cells(1, 2).Value = Cells(1, 3).Value
    
    For Rw = 2 To Cells(65536, Col).End(xlUp).Row
        
        If IsDate(Cells(Rw - 1, Col).Value) Then
            Cells(Rw, 2).Value = Cells(Rw - 1, 3).Value
            Cells(Rw, 1).Value = Cells(Rw - 1, 1).Value + 1
        Else
            Cells(Rw, 2).Value = Cells(Rw, 3).Value
            Cells(Rw, 1).Value = Cells(Rw - 1, 1).Value + 1
        End If
    Next Rw
    
    'SORT BY CREATED COLUMNS
    Cells.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2") _
            , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
            False, Orientation:=xlTopToBottom
            
    'REMVOE COLUMNS TO SORT BY
    Columns("A:B").Delete
    
TheEnd:
    Application.ScreenUpdating = False
End Sub
 
Upvote 0
Without commenting on whether the code will actually do what you want...

DataOption1 was introduced with a version of XL more recent than the one you are using. Delete the argument.

jek40 said:
I found this code that is supposed to be for sorting rows of data keeping 2 rows together.

if i run the code from a button or from tools /macro I get an error message "named arguement not found" on the line DataOption1:=xlSortNormal
{snip}
 
Upvote 0
Nimrod,

That works great.

Just one question

If I want to start at row 4 rather than row 2 what changes to the code would be required

Thanks for the help
John
 
Upvote 0
If I want to start at row 4 rather than row 2 what changes to the code would be required

Hello jek40 .. :)
So does the data you want to sort start at row 4 or is the header in Row 4 ?
 
Upvote 0
Code:
Public Sub SpecialSort()
Dim SortRng As Range

On Error GoTo TheEnd
    Application.ScreenUpdating = False
    Col = 3
    
    ' SET SORT RANGE HERE
    Set SortRng = Range("A4:IV65536")
    
    'CREATE COLUMNS TO SORT BY
    Columns("A:B").Insert Shift:=xlToRight
    
    'FILL SPECIAL SORT COLUMNS
    Cells(1, 1).Value = 1
    Cells(1, 2).Value = Cells(1, 3).Value
    
    For Rw = 2 To Cells(65536, Col).End(xlUp).Row
        
        If IsDate(Cells(Rw - 1, Col).Value) Then
            Cells(Rw, 2).Value = Cells(Rw - 1, 3).Value
            Cells(Rw, 1).Value = Cells(Rw - 1, 1).Value + 1
        Else
            Cells(Rw, 2).Value = Cells(Rw, 3).Value
            Cells(Rw, 1).Value = Cells(Rw - 1, 1).Value + 1
        End If
    Next Rw
    
    'SORT BY CREATED COLUMNS
    SortRng.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2") _
            , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
            False, Orientation:=xlTopToBottom
            
    'REMVOE COLUMNS TO SORT BY
    Columns("A:B").Delete
    
TheEnd:
    Application.ScreenUpdating = False
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top