Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 4th, 2003, 10:12 AM
Authorized User
 
Join Date: Jun 2003
Posts: 15
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to james_sellwood
Default Sorting Cells using VBA

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. :)
__________________
--------------------
James Sellwood
--------------------
 
Old June 4th, 2003, 11:02 AM
Registered User
 
Join Date: Jun 2003
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 4th, 2003, 12:43 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to ~Bean~
Default

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!!!
 
Old June 4th, 2003, 03:42 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old June 5th, 2003, 09:00 AM
Authorized User
 
Join Date: Jun 2003
Posts: 15
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to james_sellwood
Default

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 :)
--------------------
 
Old October 4th, 2012, 02:13 PM
Authorized User
 
Join Date: Sep 2012
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 7th, 2012, 01:24 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

You can also get the last row using

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

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old October 8th, 2012, 01:58 PM
Authorized User
 
Join Date: Sep 2012
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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).





Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting the cells from VBA yogeshyl Excel VBA 1 July 20th, 2007 12:26 AM
Problem with defining cells in Excel VBA sunny76 Excel VBA 6 July 6th, 2005 04:59 AM
Editing Unlocked cells in VBA hems Excel VBA 1 April 16th, 2004 10:07 AM
Editing Unlocked cells in VBA hems VB How-To 0 April 14th, 2004 11:08 AM
Editing Unlocked cells in VBA hems VBScript 1 April 14th, 2004 10:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.