PDA

View Full Version : Sort A to G and S columns



saban
06-25-2006, 01:38 PM
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

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
06-25-2006, 02:21 PM
Hi Saban,
Move Column S adjacent to Column G and include it in the sort; move it back on completion.
Regards
MD

malik641
06-25-2006, 08:22 PM
Hey saban,

Try switching the range to this:

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
Worked for me to include the S column to be selected :thumb
...didn't test it with the sort, though...

mdmackillop
06-25-2006, 10:48 PM
Hi Joseph,
You can't sort on multiple selections, so you need to move the data to be sorted.
Regards
MD

saban
06-26-2006, 02:47 AM
thnx guys will let you know how it worked

malik641
06-26-2006, 05:02 AM
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 :yes

saban
06-26-2006, 05:57 AM
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
06-26-2006, 06:09 AM
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
06-26-2006, 07:17 AM
cross posted on ozgrid.

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

saban
06-26-2006, 07:50 AM
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
06-26-2006, 08:07 AM
Type this in whatever cell you want:
=Large(H:H,1)

saban
06-26-2006, 09:13 AM
But how can I write this with VBA

mdmackillop
06-26-2006, 09:54 AM
Hi Saban,
Have a look at Formula in the VB help file.
Regards
MD

mdmackillop
06-26-2006, 09:58 AM
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.