Sorting

T

Tom

In a workbook I would like a macro to sort a column (a-z) that has blanks
rows between the data to be sorted.

Example:

Before After

NOLAND COMPANY/ATLANTA NOLAND COMPANY/ATLANTA

REPUBLIC PLUMBING SUPPLY FW WEBB COMPANY/AMHERST
FW WEBB COMPANY/AMHERST FW WEBB COMPANY/AMHERST
FW WEBB COMPANY/AMHERST FW WEBB COMPANY/AMHERST
FW WEBB COMPANY/AMHERST FW WEBB COMPANY/AMHERST
FW WEBB COMPANY/AMHERST REPUBLIC PLUMBING SUPPLY
REPUBLIC PLUMBING SUPPLY REPUBLIC PLUMBING SUPPLY

AF SUPPLY/HARRISON AF SUPPLY/HARRISON
PLOMBERIE PAYETTE & PERREAULT AF SUPPLY/HARRISON
AF SUPPLY/HARRISON AF SUPPLY/HARRISON
AF SUPPLY/HARRISON AF SUPPLY/HARRISON
AF SUPPLY/HARRISON PLOMBERIE PAYETTE & PERREAULT
PLOMBERIE PAYETTE & PERREAULT PLOMBERIE PAYETTE & PERREAULT
PLOMBERIE PAYETTE & PERREAULT PLOMBERIE PAYETTE & PERREAULT
PLOMBERIE PAYETTE & PERREAULT PLOMBERIE PAYETTE & PERREAULT


FW WEBB COMPANY/AMHERST AF SUPPLY/HARRISON
FW WEBB COMPANY/AMHERST AF SUPPLY/HARRISON
FW WEBB COMPANY/AMHERST AF SUPPLY/HARRISON
REPUBLIC PLUMBING SUPPLY FW WEBB COMPANY/AMHERST
AF SUPPLY/HARRISON FW WEBB COMPANY/AMHERST
AF SUPPLY/HARRISON FW WEBB COMPANY/AMHERST
AF SUPPLY/HARRISON REPUBLIC PLUMBING SUPPLY
AF SUPPLY/HARRISON REPUBLIC PLUMBING SUPPLY

PLOMBERIE PAYETTE & PERREAULT PLOMBERIE PAYETTE & PERREAULT
PLOMBERIE PAYETTE & PERREAULT PLOMBERIE PAYETTE & PERREAULT
PLOMBERIE PAYETTE & PERREAULT PLOMBERIE PAYETTE & PERREAULT

TFTH,
Tom
 
D

Dave Peterson

Maybe something like this if there's no gaps in columns:

Option Explicit
Sub testme()
Dim myBigRng As Range
Dim myLittleArea As Range
Dim wks As Worksheet
Dim myFormulaRng As Range

Set wks = Worksheets("sheet1")

With wks
Set myBigRng = Nothing
Set myFormulaRng = Nothing
On Error Resume Next
Set myBigRng = .Range("a1", _
.Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)
Set myFormulaRng = .Range("a1", _
.Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myFormulaRng Is Nothing Then
'keep going
Else
MsgBox "Formulas in column A--stopping!"
Exit Sub
End If

If myBigRng Is Nothing Then
MsgBox "No constants in column A!"
Exit Sub
End If

For Each myLittleArea In myBigRng.Areas
With myLittleArea.CurrentRegion
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlNo
End With
Next myLittleArea
End With

End Sub

Try it against a copy--if it doesn't work correctly, it'll destroy your data!
 
D

Dave Peterson

Does it blow up on this line:
Set wks = Worksheets("sheet1")

if yes, then change that "Sheet1" to the name of the worksheet that holds the
data.

If no, then post the line that blows up.
 
Top