Software

Ask something
Question Locked

Excel calculations

0 helpful
3 replies

by murgash

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

3 replies

by Former member

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.

by thumbsup2

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.

This discussion is closed to new replies.