VB to delete columns except these...
 

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?
--
AndrewB.

VB to delete columns except these...
 

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?

--
Rick (MVP - Excel)



"AndrewB" wrote in message
...
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?
--
AndrewB.

VB to delete columns except these...
 

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

"AndrewB" wrote in message
...
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?
--
AndrewB.

VB to delete columns except these...
 

On May 4, 3:20*am, "Don Guillett" wrote:
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
"AndrewB" wrote in message

...



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?
--
AndrewB.- Hide quoted text -

- Show quoted text -

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