Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 4th, 2003, 10:12 AM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
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
--------------------
Reply With Quote
  #2 (permalink)  
Old June 4th, 2003, 11:02 AM
Registered User
 
Join Date: Jun 2003
Location: , Wiltshire, United Kingdom.
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.
Reply With Quote
  #3 (permalink)  
Old June 4th, 2003, 12:43 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
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!!!
Reply With Quote
  #4 (permalink)  
Old June 4th, 2003, 03:42 PM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
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
Reply With Quote
  #5 (permalink)  
Old June 5th, 2003, 09:00 AM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
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 :)
--------------------
Reply With Quote
  #6 (permalink)  
Old October 4th, 2012, 02:13 PM
Authorized User
Points: 43, Level: 1
Points: 43, Level: 1 Points: 43, Level: 1 Points: 43, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #7 (permalink)  
Old October 7th, 2012, 01:24 PM
Friend of Wrox
Points: 2,950, Level: 22
Points: 2,950, Level: 22 Points: 2,950, Level: 22 Points: 2,950, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 788
Thanks: 1
Thanked 51 Times in 47 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)
Reply With Quote
  #8 (permalink)  
Old October 8th, 2012, 01:58 PM
Authorized User
Points: 43, Level: 1
Points: 43, Level: 1 Points: 43, Level: 1 Points: 43, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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).
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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



All times are GMT -4. The time now is 04:57 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.