ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Sorting (https://www.excelbanter.com/excel-worksheet-functions/67952-sorting.html)

Tom
Sorting
 
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
Sorting
 
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
Sorting
 
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
Sorting
 
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


All times are GMT +1. The time now is 07:04 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com