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

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
>
>

On Mar 15, 11:12 am, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
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!