Sorting With Blanks

jdmcmah · August 12th, 2010, 04:24 PM

Hello,

While I'm familar with programming, I think I've bitten off more than I can chew with my current assignment... we have a program that will spit out an inventory list in a marginally useful order. I'd like to write some code that will allow me to import the data, format the sheet, and then sort the output into a form that makes it a bit more useful... I can handle the imporing the file and formatting the sheet... it's the sorting that's giving me a headache.

Column A contains the category and has a blank space next to it.
Column B contains the items within that category. I can't seem to represent it visually in this post, so I'll try with text

<A2> Food <B2> Blank
<A3> Blank <B3> Cat Food
<A4> Blank <B4> Bird Food
<A5> Blank <B5> Fish Food
<A6> Accessories<B6> Blank
<A7> Blank <B7> Leash
<A8> Blank <B8> Food bowl...

I need to figure out how many rows need to be sorted, then I need to go through every row in the formatted sheet and for each cagegory in column A and sort the data in column B.

Any help would be apprecaited. :)

Thanks in advance

Shasur · August 12th, 2010, 10:05 PM

Hi

Check if the following code helps you:

Code:

Sub Sort_Ranges_With_Blanks()
Dim oBC As Range
Dim oSortArea As Range
Set oBC = Columns("A").SpecialCells(xlCellTypeBlanks)
For Each oSortArea In oBC.Areas
    Set oSortArea = oSortArea.Resize(oSortArea.Rows.Count, oSortArea.Columns.Count + 1)
    oSortArea.Sort Columns("B"), xlAscending
Next oSortArea

End Sub

Cheers
Shasur