code for sorting
 

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

code for sorting
 

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.



Jack Sons wrote:

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

code for sorting
 

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" schreef in bericht
...
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.



Jack Sons wrote:

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

code for sorting
 

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.

Jack Sons wrote:

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" schreef in bericht
...
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.



Jack Sons wrote:

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

--

Dave Peterson

code for sorting
 

Thanks a lot Dave, especially for your explanation.

Jack.

"Dave Peterson" schreef in bericht
...
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.

Jack Sons wrote:

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" schreef in bericht
...
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.



Jack Sons wrote:

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

--

Dave Peterson