Sorting Data

  • Thread starter Thread starter travelersway
  • Start date Start date
T

travelersway

I have informations for transactions:
A1:A10= date
B1:B10= Name
C1:C10 = Costs

There are rows that contain no data. Examle:
rows 1.3.4,9 may have data
rows 2,5,6,7,8,10 would be empty

I'd like to sort the data to elimated the empty rows at the following
locations and show 4 consecutive rows of data. :

A12:A22= sorted dates
B12:B22= Sorted names
C12:C22= Sorted costs



Any help is appreciated, Thanks
Travelersway
 
One way .. try tinkering with the 2 subs below

1. Sub SortAscByDateNameCost()

Clears A12:C21 first, then copies A1:C10 to A12:C21,
and then sorts A12:C21 in ascending order
by Date, then by Name, then by Cost
(source lines will be kept together)

2. Sub SortAscIndependently()

Clears A12:C21 first, then copies A1:C10 to A12:C21,
and then sorts *independently* each col within A12:C21
in ascending order (source lines will not be kept together)

To implement:
Press Alt+F11 to go to VBE
Click Insert > Module
Copy and paste the 2 subs below into the code window
Press Alt+Q to exit VBE and go back to Excel

In Excel,
Click View > Toolbars > Forms
Click on the button icon and draw a button somewhere on the sheet

The Assign Macro dialog will pop up
Look for "SortAscByDateNameCost" in the dialog box, select it > OK
(or just double-click on "SortAscByDateNameCost")
The above assigns the Sub SortAscByDateNameCost() to this button.
Right-click on the button > Edit Text [to rename the button]

Repeat to draw another button, assign "SortAscIndependently"
Right-click on the buttons to select, re-position the 2 buttons
somewhere to the right of A1:C10

Test out running the 2 subs with your sample data within A1:C10 ..
(just click the buttons)

Adapt to suit ..

'------------
Sub SortAscByDateNameCost()
Range("A12:C21").ClearContents
Range("A1:C10").Copy Destination:=Range("A12")
Range("A12:C21").Select
Selection.Sort _
Key1:=Range("A12"), _
Order1:=xlAscending, _
Key2:=Range("B12"), _
Order2:=xlAscending, _
Key3:=Range("C12"), _
Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A1").Select
End Sub

Sub SortAscIndependently()
Range("A12:C21").ClearContents
Range("A1:C10").Copy Destination:=Range("A12")
Range("A12:A21").Select
Selection.Sort Key1:=Range("A12"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("B12:B21").Select
Selection.Sort Key1:=Range("B12"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("C12:C21").Select
Selection.Sort Key1:=Range("C12"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A1").Select
End Sub
'----------
 
... I'd like to sort the data to elimated the empty rows.

If all that you're really after (going by your edited line above)
is to delete the blank rows within A1:C10,
then this sub may suffice ..

Sub RemoveBlankRows()
Range("A1:C10").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub


---
 
Max,

Thank you ,Ill try this. Is there a formula that can be written tha
would perform this automatically without the buttons?

Thanks again,

Travelerswa
 
Max, Thank you ,Ill try this.
Click to expand...

You're welcome !
Is there a formula that can be written that
would perform this automatically without the buttons?
Click to expand...

Based on your latest "edited" requirement to eliminate empty rows within a
source range, I don't think so. Formulas can only evaluate/return values in
the cells they are in, they cannot delete empty rows.

Try the Sub RemoveBlankRows() in my last response. Think it should work ok.
Just implement the sub and assign it to a forms toolbar button, as per
earlier steps provided.

---
 
Back
Top