code for sorting

J

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
 
D

Dave Peterson

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

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCol As Range

With Worksheets("Sheet1")
Set myRng = .Range("a21:p40")

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

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

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("a20:p40")
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 A20:p40 and xlyes.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top