Macro to Reverse Order of Columns but Dependent on Number of Data Rows

**francois28** · 07-09-2012, 05:37 PM

I am in need of a macro that essentially reverses the order of a set of columns and changes their formats to a couple specific formats (ex: ##+##.## and ##.##'). I am trying to eliminate manual input into excel to get the correct data I can import to Autocad. My data comes in the format of three columns (C,D,E). The problem is the data varies and can fill 20 rows or it can fill 120 rows, etc. I need the macro to be able to accomodate manipulating the data despite the amount of rows which is my main problem.

I'm attaching two sheets. One has the first macro ran which is a set of formulas I created to get the data I need. The formulas start at column P because in normals circumstances, I have various other info up to column O, but I don't utilize this.
The first macro I created works fine except it doesn't account for variable row lengths (which I need it to).
The second sheet I'm attaching shows the end result of what I need. I attempted to make a macro, but it doesn't seem to work correctly. There may be a much easier way to reverse column orders, but to my knowledge, this is the only way I know.

Any help would be greatly appreciated. And if any other information is needed, I'll be glad to provide it.
Workbook First Macro Ran.xlsm Workbook Second Macro Ran.xlsm

**francois28** · 07-10-2012, 03:57 PM

I just wanted to update this in case anyone else comes searching for a solution similar to mine. I have a little background in java and found that you can use loops an other programming language in macros. I'm not familiar with the syntax, but with some searching I was able to come up with a solution. Although it may be kind of crude, it gets the job done.

Sub MASTER MACRO()
'
' Macro4 Macro
'

'
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "pline"
    ActiveCell.Offset(1, 0).Select
Do
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-13],"","",RC[-11])"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -13))
Range("Q2").Select
Do
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-14],"","",110)"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -14))
    Range("R2").Select
Do
    ActiveCell.FormulaR1C1 = "=""LINE""&"" ""&RC[-2]&"" ""&RC[-1]&"" """
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -15))
    Range("U2").Select
Do
    ActiveCell.FormulaR1C1 = "=OFFSET(R2C3,COUNTA(C[-18])-ROW(),0)"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -18))
    Range("V2").Select
Do
    ActiveCell.FormulaR1C1 = "=OFFSET(R2C5,COUNTA(C[-17])-ROW(),0)"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -19))
    Range("W2").Select
Do
    ActiveCell.FormulaR1C1 = "=OFFSET(R2C4,COUNTA(C[-19])-ROW(),0)"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -20))
    Range("X2").Select
Do
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]<0,""LT."",""RT."")"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -21))
    Range("Y2").Select
Do
    ActiveCell.FormulaR1C1 = "-400"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -22))
    Range("Z2").Select
Do
    ActiveCell.FormulaR1C1 = "=RC[-22]+(RC[-1])"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -23))
    Range("AA2").Select
Do
    ActiveCell.FormulaR1C1 = "=RC[-1]*(-1)"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -24))
    Range("AB2").Select
Do
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-25],"","",RC[-1])"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -25))
    Range("AC2").Select
Do
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-26],"","",550)"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -26))
    Range("AD2").Select
Do
    ActiveCell.FormulaR1C1 = "=""LINE""&"" ""&RC[-2]&"" ""&RC[-1]&"" """
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -27))
Range("AB1").Select
    ActiveCell.FormulaR1C1 = "pline"
    Range("U2").Select
Do
Selection.NumberFormat = "00+00.00"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -18))
    Range("V2").Select
Do
    Selection.NumberFormat = "00.00'"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -19))
    Range("W2").Select
Do
    Selection.NumberFormat = "00.00'"
ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -20))
End Sub