Sort A to G and S columns

[saban]

[vba]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 = xlCalculationAutomatic[/vba]

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

[mdmackillop]

Hi Saban,
Move Column S adjacent to Column G and include it in the sort; move it back on completion.
Regards
MD

[malik641]

Hey saban,

Try switching the range to this:

[vba]Dim r As Long
Dim rng As String
Dim rngS As String

r = Range("c65536").End(xlUp).Row
rng = Range("a5:G" & r).Address
rngS = Range("S5:S" & r).Address

Range(rng & "," & rngS).Select[/vba]
Worked for me to include the S column to be selected
...didn't test it with the sort, though...

[mdmackillop]

Hi Joseph,
You can't sort on multiple selections, so you need to move the data to be sorted.
Regards
MD

[saban]

thnx guys will let you know how it worked

[malik641]

Hi Joseph,
You can't sort on multiple selections, so you need to move the data to be sorted.
Regards
MD

That's beat.

Well thanks MD. Now I know

[saban]

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

[malik641]

If your formula is C1 = "=B1" and you insert a column before column B (shifting everything from B to the right) then that formula will be in D1 = "=C1"

Does that answer your question?


And just try it out, anyway. If it doesn't work, just undo it or use the Replace function (if the formula isn't too complex) to get the formula you need.

[austenr]

cross posted on ozgrid.

http://www.ozgrid.com/forum/showthread.php?t=52820

[saban]

So actually it does amend the formula as needed from inserted column to the right ??

I have another question how to find the bigest value in column H (integer value)

Thnx

[malik641]

Type this in whatever cell you want:
=Large(H:H,1)

[saban]

But how can I write this with VBA

[mdmackillop]

Hi Saban,
Have a look at Formula in the VB help file.
Regards
MD

[mdmackillop]

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

Formulae should adapt as required, but if you return the column to its original location then this is not an issue in any case.