Sorting Cells using VBA

james_sellwood · June 4th, 2003, 10:12 AM

Hi, I am new to VBA in Excel although I have some basic experience with VB.NET.

I am trying to sort columns A to E on a worksheet when a button is clicked on this worksheet. The columns contain 3960 rows of data. Row 1 contains the headers for the columns and rows 2 through 3961 contain the data itself.

Please could anyone tell me how to write the VBA onClick function for the button so that the data cells in these columns are sorted by:

Column A
(Last Name)
Decending Order

then

Column B
(First Name)
Decending Order.

Thanks in advance for any help or comments you may be able to provide me. :)

PhilStollery · June 4th, 2003, 11:02 AM

Use the macro record function to find out the Excel commands, so I think you want these:

    Range("A2").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range("B2") _
        , Order2:=xlYes, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom

Add the button, and use this code. You might need to select all the cells before doing this.

~Bean~ · June 4th, 2003, 12:43 PM

The macro record feature can be a great way to get familiar with Excel VBA, just realize that Excel will put a lot of garbage in the code when you record something, and also the recorded macro is just ONE way of doing something, it may not be the best way...for example, you do not need to SELECT the cells in order to sort them...

Code:

Dim LastRow As Integer   'This is the LAST Non Empty Row

LastRow = ActiveSheet.UsedRange.Row - 1 + _
        ActiveSheet.UsedRange.Rows.Count

Worksheets("Sheet1").Range("A1:E" & LastRow & ").Sort Key1:=Worksheets("Sheet1").Columns("A"), Order1:=xlDescending, Key2:=Worksheets("Sheet1").Columns("B"), Order2:=xlDescending, Header:=xlYes

Get a LIFE???

I'm an Online Gamer...I have MULTIPLE lives!!!

SerranoG · June 4th, 2003, 03:42 PM

You also have to remember when using the macro recorder for Excel to choose whether you want Relative or Absolute Referencing or the macro will not work the way you expect it.

Gregory Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

james_sellwood · June 5th, 2003, 09:00 AM

Quote:

quote:Originally posted by ~Bean~
The macro record feature can be a great way to get familiar with Excel VBA, just realize that Excel will put a lot of garbage in the code when you record something, and also the recorded macro is just ONE way of doing something, it may not be the best way...for example, you do not need to SELECT the cells in order to sort them...

Code:

Dim LastRow As Integer   'This is the LAST Non Empty Row

LastRow = ActiveSheet.UsedRange.Row - 1 + _
        ActiveSheet.UsedRange.Rows.Count

Worksheets("Sheet1").Range("A1:E" & LastRow & ").Sort Key1:=Worksheets("Sheet1").Columns("A"), Order1:=xlDescending, Key2:=Worksheets("Sheet1").Columns("B"), Order2:=xlDescending, Header:=xlYes

:)
Thanks, I used the macro recorder to get Excel to tell me how it sorted the data just as I needed. I then used your.

Code:

Dim LastRow As Integer   'This is the LAST Non Empty Row

LastRow = ActiveSheet.UsedRange.Row - 1 + _
        ActiveSheet.UsedRange.Rows.Count

to dynamically pull out the last row to be sorted.

Great, Thanks very much to all who posted.

--------------------
:) James Sellwood :)
--------------------

jbburf · October 4th, 2012, 02:13 PM

Can you please explain the following code:

Code:

Dim LastRow As Integer   'This is the LAST Non Empty Row

LastRow = ActiveSheet.UsedRange.Row - 1 + _
        ActiveSheet.UsedRange.Rows.Count

LastRow should return the row number of the last used row, correct?

I've tried using it to refer to a specific sheet and I am receiving errors, my code is:

Code:

Sheets(data_sheet).UsedRange.Row - 1 + _ Sheets(data_sheet).UsedRange.Rows.Count

Shasur · October 7th, 2012, 01:24 PM

Hi

You can also get the last row using

LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row

Cheers
Shasur

jbburf · October 8th, 2012, 01:58 PM

Thank you for the help Shasur.

Can you explain the below code? I used it and I am getting row 24 (for a sheet with the last data being on row 23) and row 30 (for a different sheet with the last data on row 23).