Sort options by Excel VBA

[Joey11]

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:

 

[BrianB]

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

 

[Sean]

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
'------------------------------------------------------
'------------------------------------------------------

 

[Joey11]

@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

 

[Sean]

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!"

 

[Tom Ogilvy]

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
'------------------------------------------------------
'------------------------------------------------------

 

[Joey11]

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

 

[Tom Ogilvy]

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).

 

[Sean]

Maybe I missed the point here. I thought the problem was how do you sort
a range whilst retaining the initial formatting. As you cannot (as far
as I can see) "freeze" the BACKGROUND and PATTERNS cell formats, I
thought it best to either :-

1) Backup the formats, sort the range, put back the formats
2) Use conditional formatting if you formats can be defined
mathematically

I think Joey11 writing

2) PasteSpecial Paste:=xlValues -on back-up sheet-

was just a mis-typing

 

[Tom Ogilvy]

perhaps, but he seems to know the difference:

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

 

[Joey11]

It was no mistyping, they're two solutions in like that:
A) Copy the _values_ to a back-up sheet, sort them and put them back
(My solution in the last post)
B) Copy the _format_ to a back-up sheet, sort the data, put the format
back on it. (Seans solution)

I think my solution is faster , 'cause there's no need to sort the
format.
There just may be a faster solution to do this at once with such as a
function

Code:

 

[Joey11]

It was no mistyping, they're two solutions in like that:
A) Copy the _values_ to a back-up sheet, sort them and put them back
(My solution in the last post)
B) Copy the _format_ to a back-up sheet, sort the data, put the format
back on it. (Seans solution)

I think my solution is faster , 'cause there's no need to sort the
format.

There just may be a faster solution to do this at once with such as a
function

Code:

 

[Joey11]

It was no mistyping, they're two solutions in like that:
A) Copy the _values_ to a back-up sheet, sort them and put them bac
(My solution in the last post)
B) Copy the _format_ to a back-up sheet, sort the data, put the forma
back on it. (Seans solution)

I think my solution is faster , 'cause there's no need to sort th
format.

There just may be a faster solution to do this at once with such as
function

Code

 

[gably]

Hi all,

Just wondering if you can use a variable instead of a value for settin
range rows.

eg, in the above code the line:
Set rangeSORT = Rows(2:33)

Can I use a variable for the second value. (33) If the user enters mor
data, then the rows to be sorted will increase. (obviously) I've got
variable called endRow as an integer, but can't use it in th
Rows(int:int). Any suggestions would be appreciated. Cheers.

G

 

[Stan Scott]

Use set RangeSORT = Rows(INDIRECT("2:"&xxx))

Stan

 

[gably]

Stan,

Thanks, works fine now. You are the man. I haven't done any macro pro
for a while, forgot about the old & symbol. It's all coming back to m
now, slowly.

Gabe

 

[Sean]

OK Joey,
Yes, your solution is faster if you do not mind losing your formulae (if
there are any), and do not forget the "Conditional Formatting" solution
as well.

I think the answer is that your idea method :-
Code:
--------------------
Rows("3:22").Sort Sort:=XlValues
--------------------
does not exist as far as I can see in Excel (I only have up to Excel XP
so cannot answer for Excel 2003), so you will have to choose from the
existing three solutions

Good luck