Wrox Programmer Forums
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 12th, 2010, 04:24 PM
Registered User
Join Date: Aug 2010
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
Default Sorting With Blanks


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
Old August 12th, 2010, 10:05 PM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts


Check if the following code helps you:

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
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
The Following User Says Thank You to Shasur For This Useful Post:
jdmcmah (August 13th, 2010)

Similar Threads
Thread Thread Starter Forum Replies Last Post
Fill in the blanks voskoue Access VBA 4 January 30th, 2007 10:02 AM
blanks in Dreamweaver recordsets? fskilnik Dreamweaver (all versions) 8 June 1st, 2006 04:06 PM
Firing Blanks... ozPATT Access VBA 11 January 20th, 2006 11:26 AM
regular expression with numbers and blanks burdickdave ASP.NET 1.0 and 1.1 Professional 4 March 5th, 2004 09:54 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.