Sort A to G and S columns

saban123 · Jun 25th 2006

Code

r = Range("c65536").End(xlUp).Row
Range("a5:G" & r).Select
Selection.Sort Key1:=Range("G5"), Order1:=xlAscending, Key2:=Range("F5") _
, Order2:=xlAscending, Key3:=Range("C5"), Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Rows("4:" & r).Select
For i = 5 To r
Rows(i).Select
If i Mod 2 = 0 Then
Selection.Interior.ColorIndex = xlNone
Else
Selection.Interior.ColorIndex = 36
End If
Next
Range("j5").Select
Application.Calculation = xlCalculationAutomat

Display More

this sorts range from A to G and set color to each second row how can I include S column in this range
I mean that cells in column S are moved with cells from columns A to G
Any help would be apreciated
thnx

Dave Hawley · Jun 26th 2006

Re: Sort A to G and S columns

Include Column "S" in your sort range.

saban123 · Jun 26th 2006

Re: Sort A to G and S columns

Can you please tell me how cause I wrote
s= range("S5:S200").cells

but it is not working
thnx

Dave Hawley · Jun 26th 2006

Re: Sort A to G and S columns

Modify your select code to;
Range("A5:S" & r).Select

Anonymous · Jun 26th 2006

Re: Sort A to G and S columns

Dave, That surprises me. Doesn't that cover lots of columns he doesn't want? I assumed it would be the VBA equivalent of (A:G,S:S).

saban123 · Jun 26th 2006

Re: Sort A to G and S columns

Yeah that is right i dont wont to sort columns from H to R

Just from A to G and S

thnx guys for all your help

Dave Hawley · Jun 26th 2006

Re: Sort A to G and S columns

I don't know. Excel cannot sort non contigous ranges though. About the only option would be to move Column "S" to Column "H", sort, then move back.

BTW for see this page for a much better way to do alternate row coloring

saban123 · Jun 26th 2006

Re: Sort A to G and S columns

But in column G or H I already have some formulas written that means that I must rewrite some formulas coresponding to this columns is there some other way if not I will do like this

thnx for your explanation
saban

Dave Hawley · Jun 26th 2006

Re: Sort A to G and S columns

For you sort only, try

Code

r = Range("c65536").End(xlUp).Row
Range("a5:G" & r).Select
Columns("S:S").Cut
Columns("G:G").Insert Shift:=xlToRight
Range("A5:H" & r).Sort Key1:=Range("H5"), Order1:=xlAscending, Key2:=Range("F5") _
, Order2:=xlAscending, Key3:=Range("C5"), Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
Columns("G:G").Cut
Columns("S:S").Insert Shift:=xlToRight

Display More

saban123 · Jun 26th 2006

Re: Sort A to G and S columns

But this will delete content in G?? I dont want to do that

saban123 · Jun 26th 2006

Re: Sort A to G and S columns

Guys if i insert new column excel will change all the formulas coresponding to this column right?

So I dont have to rewrite all the formulas
Am I right

thnx

Anonymous · Jun 26th 2006

Re: Sort A to G and S columns

Yes. All formulas in the original sheet will have their references adjusted.

saban123 · Jun 26th 2006

Re: Sort A to G and S columns

I have another question how to find the bigest value in column H (integer value) and passed it to a variable

Thnx

Dave Hawley · Jun 27th 2006

Re: Sort A to G and S columns

Use something like

Code

lMax=WorksheetFunction.Max(Columns("H:H"))

saban123, when given code you should always run it on a copy of your data then report back with any issues.

saban123 · Jun 29th 2006

Re: Sort A to G and S columns

I used another aproach in Cell S5 I wrote Large(H5:H2000) and then pased value of this cell to a variable like max = Range("S5").Cells

But you method is more elegant but the results are the same
Thnx for all your help

saban

saban123 · Jun 30th 2006

Re: Sort A to G and S columns

why do i get error type mismatch at

Code

Set lMax = WorksheetFunction.[COLOR="Red"]Max[/COLOR](Columns("H:H"))

Dave Hawley · Jun 30th 2006

Re: Sort A to G and S columns

Because you cannot set a variable to the result of a Function. You can omly use Set when setting a variable to an Object. E.g a Worksheet.

Share