# Excel Macro for Sorting



## letmeinnow (Mar 2, 2010)

I need to create a macro to sort columns A-D in ascending order using column A. Each month there will be a different number of occupied rows within the columns.


----------



## letmeinnow (Mar 2, 2010)

I see where several people viewed request but I did not get any replies. I'm guessing it must be a stupid question but I really need this. I've tried sorting the three columns (A-D) using A as the sort criteria - in recording maco. But then when I run it on a new file I get a debug error.


----------



## bomb #21 (Jul 1, 2005)

It's not stupid. It's a bit vague though.



> when I run it on a new file I get a debug error.


Can you post the code you already have?


----------



## letmeinnow (Mar 2, 2010)

Sub Concur_Sort()
'
' Concur_Sort Macro
'
'
Columns("A").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1168")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
ActiveWorkbook.Save
End Sub


----------



## bomb #21 (Jul 1, 2005)

½ of that's Greek to me. "xlPinYin" -- 

Anyhoo, how does the below fare for you?

Sub Macro1()
x = Range("A" & Rows.Count).End(xlUp).Row
Range("A1" & x).Sort Key1:=Range("A1"), Order1:=xlAscending
End Sub


----------



## letmeinnow (Mar 2, 2010)

It worked magnificently... until I ran it in place with several macros. The macro just before this one does a concatenate using several columns. Here is the code:

Sub Concatenate()
'
' Concatenate Macro
'
'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-1],"" "",RC[-2])"
Range("C1").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C1:C19000")
Range("C1:C19000").Select
Columns("D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("C:C").Select
Selection.Copy
Columns("D").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Columns("A:C").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveWorkbook.Save
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2],RC[-1])"
Range("E1").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("E1:E19000")
Range("E1:E19000").Select
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("E:E").Select
Selection.Copy
Columns("F:F").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveWorkbook.Save
End Sub

As you can see - rows are called out there.

When I run your sort program, the sort runs great BUT the last line of the sort is on row 19000 and goes up from there. There are thousands of blank rows at the top of the file.


----------



## bomb #21 (Jul 1, 2005)

There's so much there you could cut out -- _probably_. For example:

Columns(3).Insert
Range("C1:C1900").FormulaR1C1 = "=CONCATENATE(RC[-1],"" "",RC[-2])"

in place of the first *8* actions.

I say "probably" because I don't know Excel 2007(+) code syntax. Is it possible to just describe what you have already & what you want to end up with?


----------



## letmeinnow (Mar 2, 2010)

I am not sure what you need. I am taking a text file with approx. 260 columns and creating macros to:

1. delete columns not needed
2. concatenate some columns into one column
3. sort columns left over (A-D) using column A as sort criteria
4. finish some setup


----------



## bomb #21 (Jul 1, 2005)

Sorry, I'm struggling to explain.

Seems to me that your code goes:

Insert a new column C
Create a CONCAT formula in (new) C1:C19000
*Insert a new column D
Copy C
Paste values in D*
Delete cols A-B-C

I don't understand at all. Why not just insert a new C, create the formulas, convert them to values and then delete A&B? As in:

Range("C1").EntireColumn.Insert
Range("C1:C19000").FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
Range("C:C").Value = Range("C:C").Value
Range("A:B").EntireColumn.Delete

That's 4 steps, instead of (maybe) a dozen. The steps I've put in bold in the "text version" above just seem (no offence intended but) completely pointless.

HTH to explain.


----------



## letmeinnow (Mar 2, 2010)

I know you are trying to help me. But I cannot see why rewriting my concatenate macro (which was done through macro recording) fixes the fact that the sort program you so kindly provided, ends up sorting the content properly BUT places thousands of blank rows on top.


----------



## bomb #21 (Jul 1, 2005)

For sure rewriting the concatenate macro *won't* fix the fact that the sort program (ends up sorting the content properly BUT) places thousands of blank rows on top. Nevertheless, the code in #6 appears to repeat much the same action(s) multiple times for little apparent reason; on that basis, optimizing VBA is *always* recommended. 

"thousands of blank rows on top"

*All* blank rows can be stripped out at any time by plugging in:

Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

That's not the most _efficient_ way to do it, but since I can't get a handle on your data layout ... there it is. HTH


----------



## letmeinnow (Mar 2, 2010)

You've been a doll to work with me! I do understand about the optimization. I really wish I knew more about writing macros. Thank you so much!!


----------

