The first time I'm here...

My question is about the sort function in VBA Excel.
I want my address-database yellow-white striped, but when I use a
standard sort function, the layout is sorted too.
That's why I'm looking for an extra option to escape the layout for
sorting.

Code:
--------------------
Rows("3:22").Sort Key1:=Range(first), Order1:=ord, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

--------------------

I hope there's somebody who can help my find this option!

Joey11 (Newbie)


---
Message posted from http://www.ExcelForum.com/

Looks like you need additional code to remove the stripes and replac
after sorting

--
Message posted from http://www.ExcelForum.com

What if you just copied the formatted cells to somewhere else and put
then back after sorting, like below.


Sean
"Just press the off switch, and go to sleep!"


'------------------------------------------------------
'------------------------------------------------------
Sub sort_keep_format()
Dim rangeSORT As Range, sheetTEMP As Worksheet


Set rangeSORT = Rows("3:22")

' create a worksheet to store a copy of the formats
Set sheetTEMP = rangeSORT.Worksheet.Parent.Sheets.Add
rangeSORT.Copy
sheetTEMP.Cells(1, 1).PasteSpecial Paste:=xlFormats

' set these parameters as per your wishes
rangeSORT.Sort Key1:=Range(first), Order1:=ord, Header:=xlNo,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

' put back the original formats
sheetTEMP.UsedRange.Copy
rangeSORT.PasteSpecial Paste:=xlPasteFormats

' remove the temporary worksheet
Application.DisplayAlerts = False
sheetTEMP.Delete
Application.DisplayAlerts = True

' it's good to be tidy
Set sheetTEMP = Nothing
Set rangeSORT = Nothing
End Sub
'------------------------------------------------------
'------------------------------------------------------


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

@BrianB:
That's exactly what I do now, and with my test-sheet of 19 rows is i
OK; but I thought there would be a faster method. ;)
@Sean:
I think that's more ingenious then my current solution and work
probably faster!! :)

Is there an option such as the "Paste:=xlValues" with PasteSpecial fo
Sorting? :confused

--
Message posted from http://www.ExcelForum.com

What if you used conditional formatting to colour the cells. The
condition should be :-
Condition1: Formulae is: =(MOD(ROW(),2)=0)
Condition2: Formulae is: TRUE
if you set different pattern colours for each of these conditions they
will appear as stripes (you do not even need the second condition if you
just want to leave it plain).
I am never sure if conditional formatting is quicker as it means more
calculations, but it would mean no need for any VB code.


Sean
"Just press the off switch, and go to sleep!"

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Your question is confusing. You have formulas in the range you sort and you
want to permanenlty replace those cells with the values displayed and
destroy the formulas

'------------------------------------------------------
'------------------------------------------------------
Sub sort_keep_format()
Dim rangeSORT As Range, sheetTEMP As Worksheet


Set rangeSORT = Rows("3:22")

' Change formulas to values
rngSort.Formula = rngSort.Value

' create a worksheet to store a copy of the formats
Set sheetTEMP = rangeSORT.Worksheet.Parent.Sheets.Add
rangeSORT.Copy
sheetTEMP.Cells(1, 1).PasteSpecial Paste:=xlFormats

' set these parameters as per your wishes
rangeSORT.Sort Key1:=Range(first), Order1:=ord, Header:=xlNo,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

' put back the original formats
sheetTEMP.UsedRange.Copy
rangeSORT.PasteSpecial Paste:=xlPasteFormats

' remove the temporary worksheet
Application.DisplayAlerts = False
sheetTEMP.Delete
Application.DisplayAlerts = True

' it's good to be tidy
Set sheetTEMP = Nothing
Set rangeSORT = Nothing
End Sub
'------------------------------------------------------
'------------------------------------------------------

--
Regards,
Tom Ogilvy

"Joey11 " wrote in message
...
@BrianB:
That's exactly what I do now, and with my test-sheet of 19 rows is it
OK; but I thought there would be a faster method. ;)
@Sean:
I think that's more ingenious then my current solution and works
probably faster!! :)

Is there an option such as the "Paste:=xlValues" with PasteSpecial for
Sorting?


---
Message posted from http://www.ExcelForum.com/

I'll try to be more precise:
I want to sort only values, no formulas;
problem: the layout is automaticly sorted with the values.

What I want:
A quick method to freeze the layout and sort the values in it

The current solution I have (based on Sean):
1) Copy
2) PasteSpecial Paste:=xlValues -on back-up sheet-
3) Sort
4) Copy
5) PasteSpecial Paste:=xlValues -on main sheet

--
Message posted from http://www.ExcelForum.com

Sean's code does a paste special formats on the backup sheet.

sheetTEMP.Cells(1, 1).PasteSpecial Paste:=xlFormats


If you don't want to sort formulas and they are in specific columns of your
data, you would probably have to not sort those columns - restrict you sort
to only sort the columns that don't contain formulas.


If you paste special values back on top of your data, it will overwrite your
formulas if they are include in the area pasted to. But doing a paste
special values won't restore your formatting - only a paste or paste special
formats (as Sean's code as posted does).

--
Regards,
Tom Ogilvy

"Joey11 " wrote in message
...
I'll try to be more precise:
I want to sort only values, no formulas;
problem: the layout is automaticly sorted with the values.

What I want:
A quick method to freeze the layout and sort the values in it

The current solution I have (based on Sean):
1) Copy
2) PasteSpecial Paste:=xlValues -on back-up sheet-
3) Sort
4) Copy
5) PasteSpecial Paste:=xlValues -on main sheet-


---
Message posted from http://www.ExcelForum.com/