sorting multiple-area selection

[×לי]

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.

 

[Lars Uffmann]

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

 

[Dave Peterson]

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(xlCellTypeConstants)
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

 

[Wigi]

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... ;-)

 

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

 

[×לי]

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

Eli