Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro To Delete/Add & Rename/Arrange Sheets | Excel Discussion (Misc queries) | |||
Arrange column after delete | Excel Programming | |||
Range.Select 1st pass 13 cols, 2nd paqss 25 cols twice as wide in error? | Excel Programming | |||
Cond Format:re color 2 cols, skip 2 cols | Excel Worksheet Functions | |||
Import csv files, delete rows, arrange columns .... | Excel Programming |