code for sorting

[Jack Sons]

Hi all,

I want in each column A to P to sort cells 21 to 40 (some ranges could be
empty, if cell 21 is empty so are the others in that column).
I know it can be done with a for next instruction like

For Each xxx In yyy
Do ???
Loop
Next yyy

To my regret I can't figure out what code I need.
I use Excel 2000.

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Dave Peterson]

You want each column A21:A40, B21:B40, ..., P2140 to be sorted separately?

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCol As Range

With Worksheets("Sheet1")
Set myRng = .Range("a2140")

For Each myCol In myRng.Columns
If IsEmpty(myCol.Cells(1, 1).Value) Then
'skip it
Else
With myCol
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlNo
End With
End If
Next myCol
End With
End Sub

I guessed that you don't have headers in the range to sort.

 

[Jack Sons]

Dave,

Your answer came fast as lightning, as is your code. Thank you.

About headers, they are in row 20. Makes the use of headers the code shorter
or faster?

Jack.

 

[Dave Peterson]

No speed difference as far as I know.

But the difference would be how you defined the range.

If you included the header row (20), then you'd use this line:
Set myRng = .Range("a2040")
and use xlyes in the .sort statement

====
But it would make a difference in what cell should be checked for its
emptiness. If you wanted to check row 20 (the header row), then you'd want to
use that A2040 and xlyes.

If you wanted to check the first "data" cell, you'd use the existing code.

 

[Jack Sons]

Thanks a lot Dave, especially for your explanation.

Jack.