VB to delete columns except these...

  • Thread starter Thread starter AndrewB
  • Start date Start date
A

AndrewB

Hello,

I have an Excel worksheet called "Sheet1" and I am looking for a way to
automatically delete all columns in Sheet1 that are not named "Test1" and
Test2". Can someone assist?
 
Can you describe what you mean by "columns...not named"? Do you mean
"Defined Names"? Or perhaps header text (in Row 1 I presume)? Or did you use
the word "named" in a general context meaning those text strings as cell
values in some particular (unspecified) column? Or something else entirely?
 
sub deletecolumns()
dim i as long
for i=cells(1,columns.count).end(xltoleft).column to 1 step -1
if cells(1,i)<>"Test1" and cells(1,i)<>"Test2" then columns(i).delete
next i
end sub
 
sub deletecolumns()
dim i as long
 for i=cells(1,columns.count).end(xltoleft).column to 1 step -1
   if cells(1,i)<>"Test1" and cells(1,i)<>"Test2" then columns(i).delete
 next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






- Show quoted text -
Click to expand...

Don's code suits fine then you can consider following smart code for
speed:

Only I have assumed one thing that your first row does not contain any
error value

With Cells.Rows(1)
.Replace what:="Test*", replacement:="=+na()"
.SpecialCells(xlCellTypeFormulas, xlErrors).EntireColumn.Delete
End With
 
Back
Top