Dear friends

Is it possible to select in one column only those cells with data and to
sort them with code? for example:

A1
A2 2
A3 1
A4 3
A5
A6 6
A7 5

And I want it to be:
A1
A2 1
A3 2
A4 3
A5
A6 5
A7 6

Thanks in advance for your help.

Still finetuning this, but to give you what I have so far before I go
into lunch break:

Public Sub sortNotEmpty()
Dim ws As Worksheet

Set ws = ActiveSheet

ws.Range("A1:A100").AutoFilter 1, "0"
ws.Range("A1:B100").Sort ws.Range("A1"), xlAscending
ws.AutoFilterMode = False
End Sub


This does what you want, but if the first line is empty, it puts the
sort-filter there and somehow afterwards doesn't put the empty line
back. So need to work around this somehow. Otherwise, the above function
does what you want - might need to work on the filter criteria though.

Best Regards,

Lars

If those values in column A are typed in -- not formulas, then this may work for
you:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myArea As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "No constants here!"
Exit Sub
End If

For Each myArea In myRng.Areas
With myArea
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlNo
End With
Next myArea

End Sub




??? wrote:

Dear friends

Is it possible to select in one column only those cells with data and to
sort them with code? for example:

A1
A2 2
A3 1
A4 3
A5
A6 6
A7 5

And I want it to be:
A1
A2 1
A3 2
A4 3
A5
A6 5
A7 6

Thanks in advance for your help.

--

Dave Peterson

Hi Dave

Your code sorts each area, which may or may not be what the OP wants. It
could be that the sorting musst be over all numbers in the areas.

But then the OP will tell us... ;-)

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Dave Peterson" wrote:

If those values in column A are typed in -- not formulas, then this may work for
you:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myArea As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "No constants here!"
Exit Sub
End If

For Each myArea In myRng.Areas
With myArea
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlNo
End With
Next myArea

End Sub




??? wrote:

Dear friends

Is it possible to select in one column only those cells with data and to
sort them with code? for example:

A1
A2 2
A3 1
A4 3
A5
A6 6
A7 5

And I want it to be:
A1
A2 1
A3 2
A4 3
A5
A6 5
A7 6

Thanks in advance for your help.

--

Dave Peterson

Could be...

But I'd be pretty surprised. I've seen many requests to sort each area. I'm
not sure I've ever seen anyone to sort all the data in order, but keep the empty
rows intact.



Wigi wrote:

Hi Dave

Your code sorts each area, which may or may not be what the OP wants. It
could be that the sorting musst be over all numbers in the areas.

But then the OP will tell us... ;-)

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

"Dave Peterson" wrote:

If those values in column A are typed in -- not formulas, then this may work for
you:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myArea As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "No constants here!"
Exit Sub
End If

For Each myArea In myRng.Areas
With myArea
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlNo
End With
Next myArea

End Sub




??? wrote:

Dear friends

Is it possible to select in one column only those cells with data and to
sort them with code? for example:

A1
A2 2
A3 1
A4 3
A5
A6 6
A7 5

And I want it to be:
A1
A2 1
A3 2
A4 3
A5
A6 5
A7 6

Thanks in advance for your help.

--

Dave Peterson


--

Dave Peterson

Thank you all. I have found all your suggestions perfect for me (with few
adjustments...)

Eli

"Dave Peterson" wrote:

Could be...

But I'd be pretty surprised. I've seen many requests to sort each area. I'm
not sure I've ever seen anyone to sort all the data in order, but keep the empty
rows intact.



Wigi wrote:

Hi Dave

Your code sorts each area, which may or may not be what the OP wants. It
could be that the sorting musst be over all numbers in the areas.

But then the OP will tell us... ;-)

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

"Dave Peterson" wrote:

If those values in column A are typed in -- not formulas, then this may work for
you:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myArea As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "No constants here!"
Exit Sub
End If

For Each myArea In myRng.Areas
With myArea
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlNo
End With
Next myArea

End Sub




??? wrote:

Dear friends

Is it possible to select in one column only those cells with data and to
sort them with code? for example:

A1
A2 2
A3 1
A4 3
A5
A6 6
A7 5

And I want it to be:
A1
A2 1
A3 2
A4 3
A5
A6 5
A7 6

Thanks in advance for your help.

--

Dave Peterson


--

Dave Peterson