Sorting Data

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

Thanks
Travelersway

[Max]

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
'----------

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"travelersway" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>
>
> --
> travelersway
> ------------------------------------------------------------------------
> travelersway's Profile:
http://www.excelforum.com/member.php...o&userid=17623
> View this thread: http://www.excelforum.com/showthread...hreadid=516707
>

[Max]

> ... 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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[travelersway]

Max,

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

Thanks again,

Travelersway

[Max]

"travelersway" wrote >
> Max, Thank you ,Ill try this.

You're welcome !

> Is there a formula that can be written that
> would perform this automatically without the buttons?

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---