Sort options by Excel VBA

J

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:
 
S

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


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

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
 
S

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

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

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

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
 
T

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

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-
Click to expand...

was just a mis-typing


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

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 :p , '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:
 
J

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 :p , '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:
 
J

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 :p , '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
 
G

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
 
G

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
 
S

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


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