Delete Columns not containing certain values
 

Hello,

I'm having trouble writing a VBA macro that deletes columns. I have a
spreadsheet with a bunch of columns containing various data.

What I want to do is delete every column that doesn't have either
"NO.", "QTY." or "DESCRIPTION" in it. I have about 1000 sheets that I
need to clean up and a macro would make this much easier on me.

I already have a macro which deletes the rows I don't need, now I'd
like to add code to delete the columns.

Any suggestions?

Thanks in advance,

-Nate

Delete Columns not containing certain values
 

Dim i as Long, cnt as Long, rng as Range
Dim icol as Long
set rng = Activesheet.UsedRange.Rows(1).Cells
icol = rng(rng.count).column
for i = icol to 1 step -1
cnt = Application.countif(columns(i),"*NO.*") + _
Application.countif(columns(i),"*QTY.*") + _
Application.countif(columns(i),"*DESCRIPTION*")
if cnt = 0 then
columns(i).Delete
end if
Next i

--
Regards,
Tom Ogilvy


"Nate" wrote:

Hello,

I'm having trouble writing a VBA macro that deletes columns. I have a
spreadsheet with a bunch of columns containing various data.

What I want to do is delete every column that doesn't have either
"NO.", "QTY." or "DESCRIPTION" in it. I have about 1000 sheets that I
need to clean up and a macro would make this much easier on me.

I already have a macro which deletes the rows I don't need, now I'd
like to add code to delete the columns.

Any suggestions?

Thanks in advance,

-Nate

Delete Columns not containing certain values
 

On Mar 15, 11:12 am, Tom Ogilvy
wrote:
Dim i as Long, cnt as Long, rng as Range
Dim icol as Long
set rng = Activesheet.UsedRange.Rows(1).Cells
icol = rng(rng.count).column
for i = icol to 1 step -1
cnt = Application.countif(columns(i),"*NO.*") + _
Application.countif(columns(i),"*QTY.*") + _
Application.countif(columns(i),"*DESCRIPTION*")
if cnt = 0 then
columns(i).Delete
end if
Next i

--
Regards,
Tom Ogilvy

"Nate" wrote:
Hello,

I'm having trouble writing a VBA macro that deletes columns. I have a
spreadsheet with a bunch of columns containing various data.

What I want to do is delete every column that doesn't have either
"NO.", "QTY." or "DESCRIPTION" in it. I have about 1000 sheets that I
need to clean up and a macro would make this much easier on me.

I already have a macro which deletes the rows I don't need, now I'd
like to add code to delete the columns.

Any suggestions?

Thanks in advance,

-Nate

THANK YOU!