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 Sub

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