change order of columns

Nina

New Member
Joined
May 14, 2003
Messages
19
I have a few columns downloaded from another application. That system automatically put the order of the column chronogically. I need the reverse order for my purpose. They can't be sorted because the column headings are not in date format. Is there any copy/paste funtion that will help achieve that in a short time? Thanks!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
This will reverse the activesheet's columns on the 2nd sheet in a workbook:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> sadjakls()
<SPAN style="color:darkblue">Dim</SPAN> i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>, i2 <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
<SPAN style="color:darkblue">For</SPAN> i = [iv1].End(xlToLeft).Column <SPAN style="color:darkblue">To</SPAN> 1 <SPAN style="color:darkblue">Step</SPAN> -1
    i2 = i2 + 1
    Range(Cells(1, i), Cells(65536, i).End(3)).Copy Sheets(2).Cells(1, i2)
<SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>
 

parsec

Board Regular
Joined
Aug 10, 2002
Messages
111
Hi NateO

Any idea how to achieve this kind of shorting

1 input and hold data values for column names

2 input values to be searched and replaceed when they mach the data values from the reference values from

3 copy and sort the new columns to a predefined column

Ex
When: Description (and when found copy the whole columns to column A) = Desc, catalog name, name (Then change to Description and copy to the assigned column)

When: Item Number (and when found copy the whole columns to column B) = #, catalog number, name (Then change to Item Number and copy to the assigned column)

Thank you

it sound very similar issue?

Any help is very much appreciated
john
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Here's an alternative that sorts the entire sheet in place:-

Code:
Sub ReverseColumns()
    Dim rng As Range

    Set rng = ActiveSheet.UsedRange
    rng.Rows(rng.SpecialCells(xlCellTypeLastCell).Row).Offset(1, 0).FormulaR1C1 = "=COLUMN(R[1]C)"

    Set rng = ActiveSheet.UsedRange
    rng.Sort key1:=rng.Cells(rng.Rows.Count, 1), Order1:=xlDescending, Orientation:=xlLeftToRight
    rng.Cells(rng.Rows.Count, 1).EntireRow.Delete

End Sub

Nate, I started off with this:-
Book2
ABCD
1Col3Col2Col1
2321
3321
4321
Sheet2


Ran your code and got this:-
Book2
ABCD
1Col1Col2Col1
2121
3121
4121
Sheet2
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Hi Dan,

I was assuming the active sheet was not sheets(2). That is, left the original in place and outputted a new sheet with the correct sort.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top