Software·43,882 discussions

Excel calculations

Locked

Hello,
i have a spreadsheet with 1124 rows and would like to write a macro to sort ascending every even row. For example after running the macro i want it to sort row2, then jump to and sort row4, row6 and so on..
I have digged a macro from your forum which works really nice except it sorts EVERY row in a range.
Any help will be greatly appreciated!
Here is the macro which you can revise:

Sub Sort_Test()
Dim RgToSort As Range
Dim RgRow As Range
Dim x As Long
Set RgToSort = Range(Range(“A1:AE1124”), Range(“A1:AE1124”).End(xlDown))
For Each RgRow In RgToSort.Rows
RgRow.Sort Key1:=Range(RgRow.Item(1).Address), Order1:=xlAscending, Orientation:=xlLeftToRight, OrderCustom:=1
Next
End Sub

Btw, i am using excel 2007 if it makes any difference.
Thanks in advance for every help.

Topic

Clarifications

In reply to Excel calculations

Clarifications

Excel: Lookup Function..

In reply to Excel calculations

In Excel, the Lookup function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the Lookup function:
Syntax #1

In Syntax #1, the Lookup function searches for value in the lookup_range and returns the value in the result_range that is in the same position.

The syntax for the Lookup function is:

Lookup( value, lookup_range, result_range )

value is the value to search for in the lookup_range.

lookup_range is a single row or single column of data that is sorted in ascending order. The Lookup function searches for value in this range.

result_range is a single row or single column of data that is the same size as the lookup_range. The Lookup function searches for the value in the lookup_range and returns the value from the same position in the result_range.

Note:

If the Lookup function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.

If the value is smaller than all of the values in the lookup_range, then the Lookup function will return #N/A.

If the values in the Lookup_range are not sorted in ascending order, the Lookup function will return the incorrect value.

To sort the sheets in ascending order:

1. Press Alt+F11, and then select a VBAProject (for example, press VBAProject (Financial Statements.xls)).
2. From the Insert menu, select Module.
3. In the Module, type the code lines displayed in the SheetInABC_Order macro in the screenshot.
4. Operate the macro from the Module by pressing F5.
OR
Press Alt+F11 and return to Excel, assign the macro to any Excel object as a button or icon, or press Alt+F8 to select the macro, and then click Run.

Please post back if you have any more problems or questions.

re: sorting

In reply to Excel calculations

You can’t sort just one row. You would need to copy each of the even numbered rows to a new sheet, sort them there, then use the contents of that 2nd sheet to replace the even rows on the first sheet.

[Author]

Replies