Hello all,
I'm trying to sort a range on a column containing some empty cells (to get
them last
and later get rid of these rows)

Sheet like this:

Header1 Header2 Header3 Header4 etc.
1 qw xxx
2 qw 123456 yyy
3 qw 234567 zzz
4 er aaa
5 er 345678 bbb

Range("A1:W" & LastRow).Sort(Range("C3"))
doesn't seem to do the trick.
Sometimes nothing happens, sometimes it sorts the columns although
sorting rows is default!
Any code that DOES sort a range's rows on the third column would be greatly
appreciated!
/kk

Correction; The sort key1 argument is C1, not C3 - not that it matters...

"kkarre" wrote:

Hello all,
I'm trying to sort a range on a column containing some empty cells (to get
them last
and later get rid of these rows)

Sheet like this:

Header1 Header2 Header3 Header4 etc.
1 qw xxx
2 qw 123456 yyy
3 qw 234567 zzz
4 er aaa
5 er 345678 bbb

Range("A1:W" & LastRow).Sort(Range("C3"))
doesn't seem to do the trick.
Sometimes nothing happens, sometimes it sorts the columns although
sorting rows is default!
Any code that DOES sort a range's rows on the third column would be greatly
appreciated!
/kk

This worked for me with the data you posted:

Sub AAA()
Dim lastrow As Long
lastrow = Cells(Rows.Count, _
"A").End(xlUp).Row
Range("A1:W" & lastrow).Sort _
Key1:=Range("C3"), _
Order1:=xlDescending, _
header:=xlNo
End Sub


If you actually have a header row you don't want sorted, then change to

header:=xlYes
--
Regards,
Tom Ogilvy


"kkarre" wrote:

Hello all,
I'm trying to sort a range on a column containing some empty cells (to get
them last
and later get rid of these rows)

Sheet like this:

Header1 Header2 Header3 Header4 etc.
1 qw xxx
2 qw 123456 yyy
3 qw 234567 zzz
4 er aaa
5 er 345678 bbb

Range("A1:W" & LastRow).Sort(Range("C3"))
doesn't seem to do the trick.
Sometimes nothing happens, sometimes it sorts the columns although
sorting rows is default!
Any code that DOES sort a range's rows on the third column would be greatly
appreciated!
/kk

Hello Tom,
Thanks for swift response!
I failed to mention I'm working in VSTO 2005 SE, trying to do this from
VB.NET...
I get it to work with VBA, but VB.NET will not eventhough I change all
constans to the .NET counterparts.
Have You used VSTO?
/TiA, kk

"Tom Ogilvy" wrote:

This worked for me with the data you posted:

Sub AAA()
Dim lastrow As Long
lastrow = Cells(Rows.Count, _
"A").End(xlUp).Row
Range("A1:W" & lastrow).Sort _
Key1:=Range("C3"), _
Order1:=xlDescending, _
header:=xlNo
End Sub


If you actually have a header row you don't want sorted, then change to

header:=xlYes
--
Regards,
Tom Ogilvy


"kkarre" wrote:

Hello all,
I'm trying to sort a range on a column containing some empty cells (to get
them last
and later get rid of these rows)

Sheet like this:

Header1 Header2 Header3 Header4 etc.
1 qw xxx
2 qw 123456 yyy
3 qw 234567 zzz
4 er aaa
5 er 345678 bbb

Range("A1:W" & LastRow).Sort(Range("C3"))
doesn't seem to do the trick.
Sometimes nothing happens, sometimes it sorts the columns although
sorting rows is default!
Any code that DOES sort a range's rows on the third column would be greatly
appreciated!
/kk

No, I haven't.

--
Regards,
Tom Ogilvy


"kkarre" wrote:

Hello Tom,
Thanks for swift response!
I failed to mention I'm working in VSTO 2005 SE, trying to do this from
VB.NET...
I get it to work with VBA, but VB.NET will not eventhough I change all
constans to the .NET counterparts.
Have You used VSTO?
/TiA, kk

"Tom Ogilvy" wrote:

This worked for me with the data you posted:

Sub AAA()
Dim lastrow As Long
lastrow = Cells(Rows.Count, _
"A").End(xlUp).Row
Range("A1:W" & lastrow).Sort _
Key1:=Range("C3"), _
Order1:=xlDescending, _
header:=xlNo
End Sub


If you actually have a header row you don't want sorted, then change to

header:=xlYes
--
Regards,
Tom Ogilvy


"kkarre" wrote:

Hello all,
I'm trying to sort a range on a column containing some empty cells (to get
them last
and later get rid of these rows)

Sheet like this:

Header1 Header2 Header3 Header4 etc.
1 qw xxx
2 qw 123456 yyy
3 qw 234567 zzz
4 er aaa
5 er 345678 bbb

Range("A1:W" & LastRow).Sort(Range("C3"))
doesn't seem to do the trick.
Sometimes nothing happens, sometimes it sorts the columns although
sorting rows is default!
Any code that DOES sort a range's rows on the third column would be greatly
appreciated!
/kk

Here's another example of my code (in a VSTO 2005 workbook):
Private Sub ThisWorkbook_Startup(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Startup
Try
Dim Bl As Excel.Worksheet = CType(Me.ActiveSheet, Excel.Worksheet)
Bl.Range("A1:D6").Select()
Dim x As Object = Bl.Range("A1:D6").Sort(Key1:=Bl.Cells(1, 3))
MessageBox.Show(x.ToString)
Catch ex As Exception

Finally
Me.Save()
End Try
End Sub

The messagebox


"kkarre" wrote:

Hello all,
I'm trying to sort a range on a column containing some empty cells (to get
them last
and later get rid of these rows)

Sheet like this:

Header1 Header2 Header3 Header4 etc.
1 qw xxx
2 qw 123456 yyy
3 qw 234567 zzz
4 er aaa
5 er 345678 bbb

Range("A1:W" & LastRow).Sort(Range("C3"))
doesn't seem to do the trick.
Sometimes nothing happens, sometimes it sorts the columns although
sorting rows is default!
Any code that DOES sort a range's rows on the third column would be greatly
appreciated!
/kk