Delete and re-arrange cols
 

I have data, 20 columns and approx 600 rows.
In the 1st row are column Headings.
I need to write a macro so that if a Column Heading isn't one of:
Cbt1,Cbt3,Cbt4,Cbt5,Cbt7,Account,Amount
Then the column is deleted.
The remaining columns are then re-arranged in the order:
Cbt4,Amount,Account,Cbt1,Cbt5,Cbt3,Cbt7.

Below is my effort to date, not quite working, thks

Sub CB()

lastcol = Range("IV1").End(xlToLeft).Column
MsgBox lastcol

For i = 1 To lastcol

If Cells(1, i) < "Cbt1" And Cells(1, i) < "Cbt3" And Cells(1, i) <
"Cbt4" And Cells(1, i) < "Cbt5" And Cells(1, i) < "Cbt7" And Cells(1
i) < "Account" And Cells(1, i) < "Amount" Then
Cells(1, i).EntireColumn.Delete
End If
Next
End Su

--
T De Villier
-----------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...fo&userid=2647
View this thread: http://www.excelforum.com/showthread.php?threadid=56421

Delete and re-arrange cols
 

Start from the right and work toward the left:

For i = lastcol To 1 Step -1



T De Villiers wrote:

I have data, 20 columns and approx 600 rows.
In the 1st row are column Headings.
I need to write a macro so that if a Column Heading isn't one of:
Cbt1,Cbt3,Cbt4,Cbt5,Cbt7,Account,Amount
Then the column is deleted.
The remaining columns are then re-arranged in the order:
Cbt4,Amount,Account,Cbt1,Cbt5,Cbt3,Cbt7.

Below is my effort to date, not quite working, thks

Sub CB()

lastcol = Range("IV1").End(xlToLeft).Column
MsgBox lastcol

For i = 1 To lastcol

If Cells(1, i) < "Cbt1" And Cells(1, i) < "Cbt3" And Cells(1, i) <
"Cbt4" And Cells(1, i) < "Cbt5" And Cells(1, i) < "Cbt7" And Cells(1,
i) < "Account" And Cells(1, i) < "Amount" Then
Cells(1, i).EntireColumn.Delete
End If
Next
End Sub

--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=564217

--

Dave Peterson