Sorting

[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

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

Tom wrote:
>
> 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

--

Dave Peterson

[Tom]

Dave the script did not work. I keep getting a script is out of range error
message.

"Dave Peterson" wrote:

> 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!
>
> Tom wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>

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

Tom wrote:
>
> Dave the script did not work. I keep getting a script is out of range error
> message.
>
> "Dave Peterson" wrote:
>
> > 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!
> >
> > Tom wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson