Need sort to exclude blanks

mkmed

New Member
Joined
May 14, 2007
Messages
31
Hello,

I am needing help with a sort function. My columns are parts that are populated from another sheet and some orders use 2 - 3 rows and others may use 100. I have manually sorted these for quite awhile.

Now, I want to automate the sort. If I select enough rows to be more than any order will need, I won't allow me to choose largest to smallest. Choosing Z to A works, but it leaves all the blank rows as entries.

Is there a way to make the sort function ignore any blank cells(rows) and collate the data from largest to smallest (Column C) regardless of the quantity?

Thanks for any help.

Mark
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Now, I want to automate the sort. If I select enough rows to be more than any order will need, I won't allow me to choose largest to smallest. Choosing Z to A works, but it leaves all the blank rows as entries.

I assume you're talking about a VBA macro right?

Rather than just selecting more rows than you need, you can write your code to detect the correct number of rows...

Something like

'This will find the last occupied row # in column A
LR = Cells(rows.count,"A").End(xlup).row

'This uses that row # in the range statement
Range("A1:Z" & LR).Sort......

Hope that helps..
 
Upvote 0
A macro would be fine if ... As the scarecrow says ... I only had a brain!!! I am macro naive!!! Any assistance is appreciated.

Mark
 
Upvote 0
Ok, if you're "macro naive" then the macro recorder is your friend...

Click Tools - Macro - Record New macro
Give it a name, and click ok.

Now it will record any actions you take.
go ahead and manually sort the data as you would normally do.

Then click Tools - macro - Stop recording.

Now press ALT + F11 to open the VBA Editor Window

You should find a new macro in there with the name you gave it.

Copy that text and paste it into this thread.
We can then adjust it to automatically find the correct # of rows to sort.
 
Upvote 0
Here is what I have for a small 3 line order. This sort is duplicted for other parts. These other parts are on the same rows but different groups of columns.


Here it is:
Sub Stiles_sort()
'
' Stiles_sort Macro
' This one sorts the stiles.
'
' Keyboard Shortcut: Ctrl+z
'
Range("A4:E7").Select
ActiveWorkbook.Worksheets("Cut list").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Cut list").Sort.SortFields.Add Key:=Range("E5:E7") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Cut list").Sort
.SetRange Range("A4:E7")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub


Thanks for your help!!!
Mark
 
Upvote 0
OK, so providing that macro works, and you just need to modify it so it will use the correct amount of rows...Try this

Rich (BB code):
Sub Stiles_sort()
'
' Stiles_sort Macro
' This one sorts the stiles.
'
' Keyboard Shortcut: Ctrl+z
'
'This line finds the last occupied row in column A
'And you can use that LR variable in all the following Range Statements.
LR = Cells(Rows.Count, "A").End(xlup).Row

Range("A4:E" & LR).Select
ActiveWorkbook.Worksheets("Cut list").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Cut list").Sort.SortFields.Add Key:=Range("E5:E" & LR) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Cut list").Sort
    .SetRange Range("A4:E" & LR)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

Hope that helps..
 
Upvote 0
That is still giving me the same fits I have been fighting. I went in and expanded the selection to include a larger array. It sorts fine, but does not exclude the blank cells. Here are the issues:

1. When I select many more rows (to facilitate larger orders) and some of them are blank, excel will not allow a largest to smallest sort. Only z to a. This sorts OK but it puts all the blank spaces at the top.
2. Can it be automated to not require the "control Z" key stroke? It would be nice if this just "happened" and when I click on that tab ... wala ... there they are in descending order.

Thanks again, your help is great.

Mark
 
Upvote 0
OK, maybe I'm misunderstanding...

when you say there are blanks, do you mean

A) There are blanks mixed in the range, Like say you select A1:A100, A5, A17, A30 May be blank, but the rest are not?

B) You are selecting range larger than your data, say Range A1:A100, but there is only data up to A75, A76-A100 are blank?


And are they ACTUALLY blank, or do they contain formulas that result in an apparently blank cell ??
 
Upvote 0
To clarify:

My array selection for the sort area is A4:e150.

My headers are on row 4.

My data for this small order is only rows 5,6,7

After the sort, all the rows below the header (5 on) are the blanks and the rows with my data are the last ones. 148,149 and 150. Is there a way to change this?

Thanks again,
Mark
 
Upvote 0
OK, my next guess is that your blanks are not really blank.

Look in one of the "blank" cells and check to make sure there is NOTHING in them.
Highlight the cell, press F2, then press Backspace several times to ensure it is empty.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top