Hello,

I have searched this forum for a macro that will just delete columns
based on a value in row1. It seems everyone has a twist on what they
need and I am not smart enough to extract just what I need from the
code. I am not looking for just the clearing of content, but the
actual deletion of the column.

I have a data set that may be over 200 columns long and on numerous
sheets. (the number of columns will be different in each sheet).

I am going to write a formula in Row 1 that will identify the columns
I want to keep and the ones I want to delete. To make things evident,
I am writing the formula in row 1 so the columns I want to keep will
return the word "keep" and the ones I want to delete return the word
"delete".

I would then like to delete the columns that have the word "delete" in
row 1. I have heard that some of the best macros will identify all
the columns first and then delete them all at once. Unfortunately, I
wouldn't even know where to begin. Any help would be greatly
appreciated. Thank you for your time.

The following code should do what you want. If your data is on more
than one worksheet, select all the relevant worksheets by holding down
the CTRL key and clicking the sheet tab. The code will delete from
each selected sheet those columns whose value in row 1 is "delete".

Sub DeleteColumns()
Dim WS As Worksheet
Dim R As Range
Dim DeleteThese As Range
Dim LastCol As Long
Dim C As Long

For Each WS In _
Application.ActiveWindow.SelectedSheets
Set DeleteThese = Nothing
With WS
LastCol = .Cells(1, .Columns.Count) _
.End(xlToLeft).Column
For C = LastCol To 1 Step -1
If .Cells(1, C).Value = "delete" Then
If DeleteThese Is Nothing Then
Set DeleteThese = .Columns(C)
Else
Set DeleteThese = _
Application.Union(DeleteThese, .Columns(C))
End If
End If
Next C
If Not DeleteThese Is Nothing Then
DeleteThese.Delete
End If
End With
Next WS
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






On Thu, 6 May 2010 14:18:11 -0700 (PDT), cardan
<(E-Mail Removed)> wrote:

>Hello,
>
>I have searched this forum for a macro that will just delete columns
>based on a value in row1. It seems everyone has a twist on what they
>need and I am not smart enough to extract just what I need from the
>code. I am not looking for just the clearing of content, but the
>actual deletion of the column.
>
>I have a data set that may be over 200 columns long and on numerous
>sheets. (the number of columns will be different in each sheet).
>
>I am going to write a formula in Row 1 that will identify the columns
>I want to keep and the ones I want to delete. To make things evident,
>I am writing the formula in row 1 so the columns I want to keep will
>return the word "keep" and the ones I want to delete return the word
>"delete".
>
>I would then like to delete the columns that have the word "delete" in
>row 1. I have heard that some of the best macros will identify all
>the columns first and then delete them all at once. Unfortunately, I
>wouldn't even know where to begin. Any help would be greatly
>appreciated. Thank you for your time.
>

On May 6, 3:47*pm, Chip Pearson <c...@cpearson.com> wrote:
> The following code should do what you want. If your data is on more
> than one worksheet, select all the relevant worksheets by holding down
> the CTRL key and clicking the sheet tab. The code will delete from
> each selected sheet those columns whose value in row 1 is "delete".
>
> Sub DeleteColumns()
> Dim WS As Worksheet
> Dim R As Range
> Dim DeleteThese As Range
> Dim LastCol As Long
> Dim C As Long
>
> For Each WS In _
> * * * * Application.ActiveWindow.SelectedSheets
> * * Set DeleteThese = Nothing
> * * With WS
> * * * * LastCol = .Cells(1, .Columns.Count) _
> * * * * * * * * .End(xlToLeft).Column
> * * * * For C = LastCol To 1 Step -1
> * * * * * * If .Cells(1, C).Value = "delete" Then
> * * * * * * * * If DeleteThese Is Nothing Then
> * * * * * * * * * * Set DeleteThese = .Columns(C)
> * * * * * * * * Else
> * * * * * * * * * * Set DeleteThese = _
> * * * * * * * * * * * * Application.Union(DeleteThese, .Columns(C))
> * * * * * * * * End If
> * * * * * * End If
> * * * * Next C
> * * * * If Not DeleteThese Is Nothing Then
> * * * * * * DeleteThese.Delete
> * * * * End If
> * * End With
> Next WS
> End Sub
>
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLCwww.cpearson.com
> [email on web site]
>
> On Thu, 6 May 2010 14:18:11 -0700 (PDT), cardan
>
> <carlsondan...@gmail.com> wrote:
> >Hello,
>
> >I have searched this forum for a macro that will just delete columns
> >based on a value in row1. It seems everyone has a twist on what they
> >need and I am not smart enough to extract just what I need from the
> >code. *I am not looking for just the clearing of content, but the
> >actual deletion of the column.
>
> >I have a data set that may be over 200 columns long and on numerous
> >sheets. (the number of columns will be different in each sheet).
>
> >I am going to write a formula in Row 1 that will identify the columns
> >I want to keep and the ones I want to delete. To make things evident,
> >I am writing the formula in row 1 so the columns I want to keep will
> >return the word "keep" and the ones I want to delete return the word
> >"delete".
>
> >I would then like to delete the columns that have the word "delete" in
> >row 1. *I have heard that some of the best macros will identify all
> >the columns first and then delete them all at once. *Unfortunately, I
> >wouldn't even know where to begin. *Any help would be greatly
> >appreciated. *Thank you for your time.

Hi Chip, Thank you for your response. I must apologize beforehand
because my macro skills are inferior. I pasted the code into my macro
and I cannot seem to get it work- or do anything.
Usually when I input a macro, I will cheat and record a macro and then
immediately stop recording and input the code. I tried it on this and
nothing happens. This is what it looks like in my VB. Is there
anything telling that I am doing wrong? Thanks again for your
feedback.

Sub DeleteColumns1()
'
' DeleteColumns1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Dim WS As Worksheet
Dim R As Range
Dim DeleteThese As Range
Dim LastCol As Long
Dim C As Long

For Each WS In _
Application.ActiveWindow.SelectedSheets
Set DeleteThese = Nothing
With WS
LastCol = .Cells(1, .Columns.Count) _
.End(xlToLeft).Column
For C = LastCol To 1 Step -1
If .Cells(1, C).Value = "delete" Then
If DeleteThese Is Nothing Then
Set DeleteThese = .Columns(C)
Else
Set DeleteThese = _
Application.Union(DeleteThese, .Columns(C))
End If
End If
Next C
If Not DeleteThese Is Nothing Then
DeleteThese.Delete
End If
End With
Next WS
End Sub

I copied Chip's code to Module 1 of my VBE and set up rows in an Excel
workbook with columns labeled keep and delete at random intervals. The code
worked as is to delete all columns labeled delete in row 1 and left the ones
labeled keep.

Make sure you have the code in the public module, not the sheet code module
or the ThisWorkbook code module. Press Alt + F11 to open the VB Editor. In
the small pane at the upper left of the VBE window where it says "Projects"
you will see the names of the objects in the open workbooks like Module1,
Sheet1, Sheet2, ect. through ThisWorkbook. Double click on the name
Module1. That will ensure that you have the correct code window open. Then
paste Chip's code into that window. If any of the lines of code turn red,
you have a line wrap that split a line of code and the two lines should be
made into one, or use a subscript _ line attenuator. The code will return
to default color when the line is arranged properly. To test the code,
close or diminish the VBE window and click Tools>Macro>Nacros. Then click
the macro name and click the run button.




"cardan" <(E-Mail Removed)> wrote in message
news:8771c671-a700-4a08-82f4-(E-Mail Removed)...
On May 6, 3:47 pm, Chip Pearson <c...@cpearson.com> wrote:
> The following code should do what you want. If your data is on more
> than one worksheet, select all the relevant worksheets by holding down
> the CTRL key and clicking the sheet tab. The code will delete from
> each selected sheet those columns whose value in row 1 is "delete".
>
> Sub DeleteColumns()
> Dim WS As Worksheet
> Dim R As Range
> Dim DeleteThese As Range
> Dim LastCol As Long
> Dim C As Long
>
> For Each WS In _
> Application.ActiveWindow.SelectedSheets
> Set DeleteThese = Nothing
> With WS
> LastCol = .Cells(1, .Columns.Count) _
> .End(xlToLeft).Column
> For C = LastCol To 1 Step -1
> If .Cells(1, C).Value = "delete" Then
> If DeleteThese Is Nothing Then
> Set DeleteThese = .Columns(C)
> Else
> Set DeleteThese = _
> Application.Union(DeleteThese, .Columns(C))
> End If
> End If
> Next C
> If Not DeleteThese Is Nothing Then
> DeleteThese.Delete
> End If
> End With
> Next WS
> End Sub
>
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLCwww.cpearson.com
> [email on web site]
>
> On Thu, 6 May 2010 14:18:11 -0700 (PDT), cardan
>
> <carlsondan...@gmail.com> wrote:
> >Hello,
>
> >I have searched this forum for a macro that will just delete columns
> >based on a value in row1. It seems everyone has a twist on what they
> >need and I am not smart enough to extract just what I need from the
> >code. I am not looking for just the clearing of content, but the
> >actual deletion of the column.
>
> >I have a data set that may be over 200 columns long and on numerous
> >sheets. (the number of columns will be different in each sheet).
>
> >I am going to write a formula in Row 1 that will identify the columns
> >I want to keep and the ones I want to delete. To make things evident,
> >I am writing the formula in row 1 so the columns I want to keep will
> >return the word "keep" and the ones I want to delete return the word
> >"delete".
>
> >I would then like to delete the columns that have the word "delete" in
> >row 1. I have heard that some of the best macros will identify all
> >the columns first and then delete them all at once. Unfortunately, I
> >wouldn't even know where to begin. Any help would be greatly
> >appreciated. Thank you for your time.

Hi Chip, Thank you for your response. I must apologize beforehand
because my macro skills are inferior. I pasted the code into my macro
and I cannot seem to get it work- or do anything.
Usually when I input a macro, I will cheat and record a macro and then
immediately stop recording and input the code. I tried it on this and
nothing happens. This is what it looks like in my VB. Is there
anything telling that I am doing wrong? Thanks again for your
feedback.

Sub DeleteColumns1()
'
' DeleteColumns1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Dim WS As Worksheet
Dim R As Range
Dim DeleteThese As Range
Dim LastCol As Long
Dim C As Long

For Each WS In _
Application.ActiveWindow.SelectedSheets
Set DeleteThese = Nothing
With WS
LastCol = .Cells(1, .Columns.Count) _
.End(xlToLeft).Column
For C = LastCol To 1 Step -1
If .Cells(1, C).Value = "delete" Then
If DeleteThese Is Nothing Then
Set DeleteThese = .Columns(C)
Else
Set DeleteThese = _
Application.Union(DeleteThese, .Columns(C))
End If
End If
Next C
If Not DeleteThese Is Nothing Then
DeleteThese.Delete
End If
End With
Next WS
End Sub

[mobell]

Sub Macro1()

Dim stTarget As Worksheet, iColumnIndex As Integer




For Each stTarget In Worksheets

For iColumnIndex = 256 To 1 Step -1

If UCase(stTarget.Cells(1, iColumnIndex).Text) = "DELETE" Then

stTarget.Columns(iColumnIndex).Delete Shift:=xlToLeft

End If

Next

Next

End Sub

>Hi Chip, Thank you for your response. I must apologize beforehand
>because my macro skills are inferior. I pasted the code into my macro
>and I cannot seem to get it work- or do anything.


See http://www.cpearson.com/excel/WhereToPutTheCode.aspx for details
about where to put and how to use code that you find here and other
venues. It explains the differences between snippets, procedures, and
modules, how they relate to one another, and how to use them.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Thu, 6 May 2010 17:12:32 -0700 (PDT), cardan
<(E-Mail Removed)> wrote:

>On May 6, 3:47*pm, Chip Pearson <c...@cpearson.com> wrote:
>> The following code should do what you want. If your data is on more
>> than one worksheet, select all the relevant worksheets by holding down
>> the CTRL key and clicking the sheet tab. The code will delete from
>> each selected sheet those columns whose value in row 1 is "delete".
>>
>> Sub DeleteColumns()
>> Dim WS As Worksheet
>> Dim R As Range
>> Dim DeleteThese As Range
>> Dim LastCol As Long
>> Dim C As Long
>>
>> For Each WS In _
>> * * * * Application.ActiveWindow.SelectedSheets
>> * * Set DeleteThese = Nothing
>> * * With WS
>> * * * * LastCol = .Cells(1, .Columns.Count) _
>> * * * * * * * * .End(xlToLeft).Column
>> * * * * For C = LastCol To 1 Step -1
>> * * * * * * If .Cells(1, C).Value = "delete" Then
>> * * * * * * * * If DeleteThese Is Nothing Then
>> * * * * * * * * * * Set DeleteThese = .Columns(C)
>> * * * * * * * * Else
>> * * * * * * * * * * Set DeleteThese = _
>> * * * * * * * * * * * * Application.Union(DeleteThese, .Columns(C))
>> * * * * * * * * End If
>> * * * * * * End If
>> * * * * Next C
>> * * * * If Not DeleteThese Is Nothing Then
>> * * * * * * DeleteThese.Delete
>> * * * * End If
>> * * End With
>> Next WS
>> End Sub
>>
>> Cordially,
>> Chip Pearson
>> Microsoft MVP 1998 - 2010
>> Pearson Software Consulting, LLCwww.cpearson.com
>> [email on web site]
>>
>> On Thu, 6 May 2010 14:18:11 -0700 (PDT), cardan
>>
>> <carlsondan...@gmail.com> wrote:
>> >Hello,
>>
>> >I have searched this forum for a macro that will just delete columns
>> >based on a value in row1. It seems everyone has a twist on what they
>> >need and I am not smart enough to extract just what I need from the
>> >code. *I am not looking for just the clearing of content, but the
>> >actual deletion of the column.
>>
>> >I have a data set that may be over 200 columns long and on numerous
>> >sheets. (the number of columns will be different in each sheet).
>>
>> >I am going to write a formula in Row 1 that will identify the columns
>> >I want to keep and the ones I want to delete. To make things evident,
>> >I am writing the formula in row 1 so the columns I want to keep will
>> >return the word "keep" and the ones I want to delete return the word
>> >"delete".
>>
>> >I would then like to delete the columns that have the word "delete" in
>> >row 1. *I have heard that some of the best macros will identify all
>> >the columns first and then delete them all at once. *Unfortunately, I
>> >wouldn't even know where to begin. *Any help would be greatly
>> >appreciated. *Thank you for your time.
>
>Hi Chip, Thank you for your response. I must apologize beforehand
>because my macro skills are inferior. I pasted the code into my macro
>and I cannot seem to get it work- or do anything.
>Usually when I input a macro, I will cheat and record a macro and then
>immediately stop recording and input the code. I tried it on this and
>nothing happens. This is what it looks like in my VB. Is there
>anything telling that I am doing wrong? Thanks again for your
>feedback.
>
>Sub DeleteColumns1()
>'
>' DeleteColumns1 Macro
>'
>' Keyboard Shortcut: Ctrl+Shift+M
>'
>Dim WS As Worksheet
>Dim R As Range
>Dim DeleteThese As Range
>Dim LastCol As Long
>Dim C As Long
>
>For Each WS In _
> Application.ActiveWindow.SelectedSheets
> Set DeleteThese = Nothing
> With WS
> LastCol = .Cells(1, .Columns.Count) _
> .End(xlToLeft).Column
> For C = LastCol To 1 Step -1
> If .Cells(1, C).Value = "delete" Then
> If DeleteThese Is Nothing Then
> Set DeleteThese = .Columns(C)
> Else
> Set DeleteThese = _
> Application.Union(DeleteThese, .Columns(C))
> End If
> End If
> Next C
> If Not DeleteThese Is Nothing Then
> DeleteThese.Delete
> End If
> End With
>Next WS
>End Sub
>
>
>
>
>
>
>
>

On May 7, 6:01*am, "JLGWhiz" <JLGW...@cfl.rr.com> wrote:
> I copied Chip's code to Module 1 of my VBE and set up rows in an Excel
> workbook with columns labeled keep and delete at random intervals. *Thecode
> worked as is to delete all columns labeled delete in row 1 and left the ones
> labeled keep.
>
> Make sure you have the code in the public module, not the sheet code module
> or the ThisWorkbook code module. *Press Alt + F11 to open the VB Editor.. *In
> the small pane at the upper left of the VBE window where it says "Projects"
> you will see the names of the objects in the open workbooks like Module1,
> Sheet1, Sheet2, ect. through ThisWorkbook. *Double click on the name
> Module1. *That will ensure that you have the correct code window open. *Then
> paste Chip's code into that window. *If any of the lines of code turn red,
> you have a line wrap that split a line of code and the two lines should be
> made into one, or use a subscript *_ *line attenuator. *The code will return
> to default color when the line is arranged properly. *To test the code,
> close or diminish the VBE window and click Tools>Macro>Nacros. Then click
> the macro name and click the run button.
>
> "cardan" <carlsondan...@gmail.com> wrote in message
>
> news:8771c671-a700-4a08-82f4-(E-Mail Removed)...
> On May 6, 3:47 pm, Chip Pearson <c...@cpearson.com> wrote:
>
>
>
>
>
> > The following code should do what you want. If your data is on more
> > than one worksheet, select all the relevant worksheets by holding down
> > the CTRL key and clicking the sheet tab. The code will delete from
> > each selected sheet those columns whose value in row 1 is "delete".
>
> > Sub DeleteColumns()
> > Dim WS As Worksheet
> > Dim R As Range
> > Dim DeleteThese As Range
> > Dim LastCol As Long
> > Dim C As Long
>
> > For Each WS In _
> > Application.ActiveWindow.SelectedSheets
> > Set DeleteThese = Nothing
> > With WS
> > LastCol = .Cells(1, .Columns.Count) _
> > .End(xlToLeft).Column
> > For C = LastCol To 1 Step -1
> > If .Cells(1, C).Value = "delete" Then
> > If DeleteThese Is Nothing Then
> > Set DeleteThese = .Columns(C)
> > Else
> > Set DeleteThese = _
> > Application.Union(DeleteThese, .Columns(C))
> > End If
> > End If
> > Next C
> > If Not DeleteThese Is Nothing Then
> > DeleteThese.Delete
> > End If
> > End With
> > Next WS
> > End Sub
>
> > Cordially,
> > Chip Pearson
> > Microsoft MVP 1998 - 2010
> > Pearson Software Consulting, LLCwww.cpearson.com
> > [email on web site]
>
> > On Thu, 6 May 2010 14:18:11 -0700 (PDT), cardan
>
> > <carlsondan...@gmail.com> wrote:
> > >Hello,
>
> > >I have searched this forum for a macro that will just delete columns
> > >based on a value in row1. It seems everyone has a twist on what they
> > >need and I am not smart enough to extract just what I need from the
> > >code. I am not looking for just the clearing of content, but the
> > >actual deletion of the column.
>
> > >I have a data set that may be over 200 columns long and on numerous
> > >sheets. (the number of columns will be different in each sheet).
>
> > >I am going to write a formula in Row 1 that will identify the columns
> > >I want to keep and the ones I want to delete. To make things evident,
> > >I am writing the formula in row 1 so the columns I want to keep will
> > >return the word "keep" and the ones I want to delete return the word
> > >"delete".
>
> > >I would then like to delete the columns that have the word "delete" in
> > >row 1. I have heard that some of the best macros will identify all
> > >the columns first and then delete them all at once. Unfortunately, I
> > >wouldn't even know where to begin. Any help would be greatly
> > >appreciated. Thank you for your time.
>
> Hi Chip, Thank you for your response. I must apologize beforehand
> because my macro skills are inferior. I pasted the code into my macro
> and I cannot seem to get it work- or do anything.
> Usually when I input a macro, I will cheat and record a macro and then
> immediately stop recording and input the code. I tried it on this and
> nothing happens. This is what it looks like in my VB. Is there
> anything telling that I am doing wrong? *Thanks again for your
> feedback.
>
> Sub DeleteColumns1()
> '
> ' DeleteColumns1 Macro
> '
> ' Keyboard Shortcut: Ctrl+Shift+M
> '
> Dim WS As Worksheet
> Dim R As Range
> Dim DeleteThese As Range
> Dim LastCol As Long
> Dim C As Long
>
> For Each WS In _
> * * * * Application.ActiveWindow.SelectedSheets
> * * Set DeleteThese = Nothing
> * * With WS
> * * * * LastCol = .Cells(1, .Columns.Count) _
> * * * * * * * * .End(xlToLeft).Column
> * * * * For C = LastCol To 1 Step -1
> * * * * * * If .Cells(1, C).Value = "delete" Then
> * * * * * * * * If DeleteThese Is Nothing Then
> * * * * * * * * * * Set DeleteThese = .Columns(C)
> * * * * * * * * Else
> * * * * * * * * * * Set DeleteThese = _
> * * * * * * * * * * * * Application.Union(DeleteThese, .Columns(C))
> * * * * * * * * End If
> * * * * * * End If
> * * * * Next C
> * * * * If Not DeleteThese Is Nothing Then
> * * * * * * DeleteThese.Delete
> * * * * End If
> * * End With
> Next WS
> End Sub- Hide quoted text -
>
> - Show quoted text -

If formula used for identifying the columns to delete.Then some
problem may be because of dynamic deletion of column the cells where
"keep" displayed may display "delete" as reference is changed.
Just Check.

I faced similar situation some days ago.If possible kindly fwd the
samle sheet..

On May 6, 6:01*pm, "JLGWhiz" <JLGW...@cfl.rr.com> wrote:
> I copied Chip's code to Module 1 of my VBE and set up rows in an Excel
> workbook with columns labeled keep and delete at random intervals. *Thecode
> worked as is to delete all columns labeled delete in row 1 and left the ones
> labeled keep.
>
> Make sure you have the code in the public module, not the sheet code module
> or the ThisWorkbook code module. *Press Alt + F11 to open the VB Editor.. *In
> the small pane at the upper left of the VBE window where it says "Projects"
> you will see the names of the objects in the open workbooks like Module1,
> Sheet1, Sheet2, ect. through ThisWorkbook. *Double click on the name
> Module1. *That will ensure that you have the correct code window open. *Then
> paste Chip's code into that window. *If any of the lines of code turn red,
> you have a line wrap that split a line of code and the two lines should be
> made into one, or use a subscript *_ *line attenuator. *The code will return
> to default color when the line is arranged properly. *To test the code,
> close or diminish the VBE window and click Tools>Macro>Nacros. Then click
> the macro name and click the run button.
>
> "cardan" <carlsondan...@gmail.com> wrote in message
>
> news:8771c671-a700-4a08-82f4-(E-Mail Removed)...
> On May 6, 3:47 pm, Chip Pearson <c...@cpearson.com> wrote:
>
>
>
> > The following code should do what you want. If your data is on more
> > than one worksheet, select all the relevant worksheets by holding down
> > the CTRL key and clicking the sheet tab. The code will delete from
> > each selected sheet those columns whose value in row 1 is "delete".
>
> > Sub DeleteColumns()
> > Dim WS As Worksheet
> > Dim R As Range
> > Dim DeleteThese As Range
> > Dim LastCol As Long
> > Dim C As Long
>
> > For Each WS In _
> > Application.ActiveWindow.SelectedSheets
> > Set DeleteThese = Nothing
> > With WS
> > LastCol = .Cells(1, .Columns.Count) _
> > .End(xlToLeft).Column
> > For C = LastCol To 1 Step -1
> > If .Cells(1, C).Value = "delete" Then
> > If DeleteThese Is Nothing Then
> > Set DeleteThese = .Columns(C)
> > Else
> > Set DeleteThese = _
> > Application.Union(DeleteThese, .Columns(C))
> > End If
> > End If
> > Next C
> > If Not DeleteThese Is Nothing Then
> > DeleteThese.Delete
> > End If
> > End With
> > Next WS
> > End Sub
>
> > Cordially,
> > Chip Pearson
> > Microsoft MVP 1998 - 2010
> > Pearson Software Consulting, LLCwww.cpearson.com
> > [email on web site]
>
> > On Thu, 6 May 2010 14:18:11 -0700 (PDT), cardan
>
> > <carlsondan...@gmail.com> wrote:
> > >Hello,
>
> > >I have searched this forum for a macro that will just delete columns
> > >based on a value in row1. It seems everyone has a twist on what they
> > >need and I am not smart enough to extract just what I need from the
> > >code. I am not looking for just the clearing of content, but the
> > >actual deletion of the column.
>
> > >I have a data set that may be over 200 columns long and on numerous
> > >sheets. (the number of columns will be different in each sheet).
>
> > >I am going to write a formula in Row 1 that will identify the columns
> > >I want to keep and the ones I want to delete. To make things evident,
> > >I am writing the formula in row 1 so the columns I want to keep will
> > >return the word "keep" and the ones I want to delete return the word
> > >"delete".
>
> > >I would then like to delete the columns that have the word "delete" in
> > >row 1. I have heard that some of the best macros will identify all
> > >the columns first and then delete them all at once. Unfortunately, I
> > >wouldn't even know where to begin. Any help would be greatly
> > >appreciated. Thank you for your time.
>
> Hi Chip, Thank you for your response. I must apologize beforehand
> because my macro skills are inferior. I pasted the code into my macro
> and I cannot seem to get it work- or do anything.
> Usually when I input a macro, I will cheat and record a macro and then
> immediately stop recording and input the code. I tried it on this and
> nothing happens. This is what it looks like in my VB. Is there
> anything telling that I am doing wrong? *Thanks again for your
> feedback.
>
> Sub DeleteColumns1()
> '
> ' DeleteColumns1 Macro
> '
> ' Keyboard Shortcut: Ctrl+Shift+M
> '
> Dim WS As Worksheet
> Dim R As Range
> Dim DeleteThese As Range
> Dim LastCol As Long
> Dim C As Long
>
> For Each WS In _
> * * * * Application.ActiveWindow.SelectedSheets
> * * Set DeleteThese = Nothing
> * * With WS
> * * * * LastCol = .Cells(1, .Columns.Count) _
> * * * * * * * * .End(xlToLeft).Column
> * * * * For C = LastCol To 1 Step -1
> * * * * * * If .Cells(1, C).Value = "delete" Then
> * * * * * * * * If DeleteThese Is Nothing Then
> * * * * * * * * * * Set DeleteThese = .Columns(C)
> * * * * * * * * Else
> * * * * * * * * * * Set DeleteThese = _
> * * * * * * * * * * * * Application.Union(DeleteThese, .Columns(C))
> * * * * * * * * End If
> * * * * * * End If
> * * * * Next C
> * * * * If Not DeleteThese Is Nothing Then
> * * * * * * DeleteThese.Delete
> * * * * End If
> * * End With
> Next WS
> End Sub

I followed the instructions and it works great! The only question I
have is how do I insert a shortcut- or can I with this module? Thanks
again

On May 7, 4:22*am, Javed <asadullaja...@gmail.com> wrote:
> On May 7, 6:01*am, "JLGWhiz" <JLGW...@cfl.rr.com> wrote:
>
>
>
> > I copied Chip's code to Module 1 of my VBE and set up rows in an Excel
> > workbook with columns labeled keep and delete at random intervals. *The code
> > worked as is to delete all columns labeled delete in row 1 and left theones
> > labeled keep.
>
> > Make sure you have the code in the public module, not the sheet code module
> > or the ThisWorkbook code module. *Press Alt + F11 to open the VB Editor. *In
> > the small pane at the upper left of the VBE window where it says "Projects"
> > you will see the names of the objects in the open workbooks like Module1,
> > Sheet1, Sheet2, ect. through ThisWorkbook. *Double click on the name
> > Module1. *That will ensure that you have the correct code window open.. *Then
> > paste Chip's code into that window. *If any of the lines of code turnred,
> > you have a line wrap that split a line of code and the two lines shouldbe
> > made into one, or use a subscript *_ *line attenuator. *The code will return
> > to default color when the line is arranged properly. *To test the code,
> > close or diminish the VBE window and click Tools>Macro>Nacros. Then click
> > the macro name and click the run button.
>
> > "cardan" <carlsondan...@gmail.com> wrote in message
>
> >news:8771c671-a700-4a08-82f4-(E-Mail Removed)....
> > On May 6, 3:47 pm, Chip Pearson <c...@cpearson.com> wrote:
>
> > > The following code should do what you want. If your data is on more
> > > than one worksheet, select all the relevant worksheets by holding down
> > > the CTRL key and clicking the sheet tab. The code will delete from
> > > each selected sheet those columns whose value in row 1 is "delete".
>
> > > Sub DeleteColumns()
> > > Dim WS As Worksheet
> > > Dim R As Range
> > > Dim DeleteThese As Range
> > > Dim LastCol As Long
> > > Dim C As Long
>
> > > For Each WS In _
> > > Application.ActiveWindow.SelectedSheets
> > > Set DeleteThese = Nothing
> > > With WS
> > > LastCol = .Cells(1, .Columns.Count) _
> > > .End(xlToLeft).Column
> > > For C = LastCol To 1 Step -1
> > > If .Cells(1, C).Value = "delete" Then
> > > If DeleteThese Is Nothing Then
> > > Set DeleteThese = .Columns(C)
> > > Else
> > > Set DeleteThese = _
> > > Application.Union(DeleteThese, .Columns(C))
> > > End If
> > > End If
> > > Next C
> > > If Not DeleteThese Is Nothing Then
> > > DeleteThese.Delete
> > > End If
> > > End With
> > > Next WS
> > > End Sub
>
> > > Cordially,
> > > Chip Pearson
> > > Microsoft MVP 1998 - 2010
> > > Pearson Software Consulting, LLCwww.cpearson.com
> > > [email on web site]
>
> > > On Thu, 6 May 2010 14:18:11 -0700 (PDT), cardan
>
> > > <carlsondan...@gmail.com> wrote:
> > > >Hello,
>
> > > >I have searched this forum for a macro that will just delete columns
> > > >based on a value in row1. It seems everyone has a twist on what they
> > > >need and I am not smart enough to extract just what I need from the
> > > >code. I am not looking for just the clearing of content, but the
> > > >actual deletion of the column.
>
> > > >I have a data set that may be over 200 columns long and on numerous
> > > >sheets. (the number of columns will be different in each sheet).
>
> > > >I am going to write a formula in Row 1 that will identify the columns
> > > >I want to keep and the ones I want to delete. To make things evident,
> > > >I am writing the formula in row 1 so the columns I want to keep will
> > > >return the word "keep" and the ones I want to delete return the word
> > > >"delete".
>
> > > >I would then like to delete the columns that have the word "delete" in
> > > >row 1. I have heard that some of the best macros will identify all
> > > >the columns first and then delete them all at once. Unfortunately, I
> > > >wouldn't even know where to begin. Any help would be greatly
> > > >appreciated. Thank you for your time.
>
> > Hi Chip, Thank you for your response. I must apologize beforehand
> > because my macro skills are inferior. I pasted the code into my macro
> > and I cannot seem to get it work- or do anything.
> > Usually when I input a macro, I will cheat and record a macro and then
> > immediately stop recording and input the code. I tried it on this and
> > nothing happens. This is what it looks like in my VB. Is there
> > anything telling that I am doing wrong? *Thanks again for your
> > feedback.
>
> > Sub DeleteColumns1()
> > '
> > ' DeleteColumns1 Macro
> > '
> > ' Keyboard Shortcut: Ctrl+Shift+M
> > '
> > Dim WS As Worksheet
> > Dim R As Range
> > Dim DeleteThese As Range
> > Dim LastCol As Long
> > Dim C As Long
>
> > For Each WS In _
> > * * * * Application.ActiveWindow.SelectedSheets
> > * * Set DeleteThese = Nothing
> > * * With WS
> > * * * * LastCol = .Cells(1, .Columns.Count) _
> > * * * * * * * * .End(xlToLeft).Column
> > * * * * For C = LastCol To 1 Step -1
> > * * * * * * If .Cells(1, C).Value = "delete" Then
> > * * * * * * * * If DeleteThese Is Nothing Then
> > * * * * * * * * * * Set DeleteThese = .Columns(C)
> > * * * * * * * * Else
> > * * * * * * * * * * Set DeleteThese = _
> > * * * * * * * * * * * * Application.Union(DeleteThese, .Columns(C))
> > * * * * * * * * End If
> > * * * * * * End If
> > * * * * Next C
> > * * * * If Not DeleteThese Is Nothing Then
> > * * * * * * DeleteThese.Delete
> > * * * * End If
> > * * End With
> > Next WS
> > End Sub- Hide quoted text -
>
> > - Show quoted text -
>
> If formula used for identifying the columns to delete.Then some
> problem may be because of dynamic deletion of column the cells where
> "keep" displayed may display "delete" as reference is changed.
> Just Check.
>
> I faced similar situation some days ago.If possible kindly fwd the
> samle sheet..

Hi Javed, Thanks for the reply. The problem shouldn't be the
formula. The formula looks at a range of numbers in another sheet and
compares it to a number in the same column as the formula so there
isn't a reference error. I just followed JLGWhiz' advice and put it
into the correct module. It is working well now. Thanks for the
reply!

On May 7, 4:16*am, Chip Pearson <c...@cpearson.com> wrote:
> >Hi Chip, Thank you for your response. I must apologize beforehand
> >because my macro skills are inferior. I pasted the code into my macro
> >and I cannot seem to get it work- or do anything.
>
> Seehttp://www.cpearson.com/excel/WhereToPutTheCode.aspxfor details
> about where to put and how to use code that you find here and other
> venues. It explains the differences between snippets, procedures, and
> modules, how they relate to one another, and how to use them.
>
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLCwww.cpearson.com
> [email on web site]
>
> On Thu, 6 May 2010 17:12:32 -0700 (PDT), cardan
>
> <carlsondan...@gmail.com> wrote:
> >On May 6, 3:47*pm, Chip Pearson <c...@cpearson.com> wrote:
> >> The following code should do what you want. If your data is on more
> >> than one worksheet, select all the relevant worksheets by holding down
> >> the CTRL key and clicking the sheet tab. The code will delete from
> >> each selected sheet those columns whose value in row 1 is "delete".
>
> >> Sub DeleteColumns()
> >> Dim WS As Worksheet
> >> Dim R As Range
> >> Dim DeleteThese As Range
> >> Dim LastCol As Long
> >> Dim C As Long
>
> >> For Each WS In _
> >> * * * * Application.ActiveWindow.SelectedSheets
> >> * * Set DeleteThese = Nothing
> >> * * With WS
> >> * * * * LastCol = .Cells(1, .Columns.Count) _
> >> * * * * * * * * .End(xlToLeft).Column
> >> * * * * For C = LastCol To 1 Step -1
> >> * * * * * * If .Cells(1, C).Value = "delete" Then
> >> * * * * * * * * If DeleteThese Is Nothing Then
> >> * * * * * * * * * * Set DeleteThese = .Columns(C)
> >> * * * * * * * * Else
> >> * * * * * * * * * * Set DeleteThese = _
> >> * * * * * * * * * * * * Application.Union(DeleteThese, .Columns(C))
> >> * * * * * * * * End If
> >> * * * * * * End If
> >> * * * * Next C
> >> * * * * If Not DeleteThese Is Nothing Then
> >> * * * * * * DeleteThese.Delete
> >> * * * * End If
> >> * * End With
> >> Next WS
> >> End Sub
>
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP 1998 - 2010
> >> Pearson Software Consulting, LLCwww.cpearson.com
> >> [email on web site]
>
> >> On Thu, 6 May 2010 14:18:11 -0700 (PDT), cardan
>
> >> <carlsondan...@gmail.com> wrote:
> >> >Hello,
>
> >> >I have searched this forum for a macro that will just delete columns
> >> >based on a value in row1. It seems everyone has a twist on what they
> >> >need and I am not smart enough to extract just what I need from the
> >> >code. *I am not looking for just the clearing of content, but the
> >> >actual deletion of the column.
>
> >> >I have a data set that may be over 200 columns long and on numerous
> >> >sheets. (the number of columns will be different in each sheet).
>
> >> >I am going to write a formula in Row 1 that will identify the columns
> >> >I want to keep and the ones I want to delete. To make things evident,
> >> >I am writing the formula in row 1 so the columns I want to keep will
> >> >return the word "keep" and the ones I want to delete return the word
> >> >"delete".
>
> >> >I would then like to delete the columns that have the word "delete" in
> >> >row 1. *I have heard that some of the best macros will identify all
> >> >the columns first and then delete them all at once. *Unfortunately,I
> >> >wouldn't even know where to begin. *Any help would be greatly
> >> >appreciated. *Thank you for your time.
>
> >Hi Chip, Thank you for your response. I must apologize beforehand
> >because my macro skills are inferior. I pasted the code into my macro
> >and I cannot seem to get it work- or do anything.
> >Usually when I input a macro, I will cheat and record a macro and then
> >immediately stop recording and input the code. I tried it on this and
> >nothing happens. This is what it looks like in my VB. Is there
> >anything telling that I am doing wrong? *Thanks again for your
> >feedback.
>
> >Sub DeleteColumns1()
> >'
> >' DeleteColumns1 Macro
> >'
> >' Keyboard Shortcut: Ctrl+Shift+M
> >'
> >Dim WS As Worksheet
> >Dim R As Range
> >Dim DeleteThese As Range
> >Dim LastCol As Long
> >Dim C As Long
>
> >For Each WS In _
> > * * * *Application.ActiveWindow.SelectedSheets
> > * *Set DeleteThese = Nothing
> > * *With WS
> > * * * *LastCol = .Cells(1, .Columns.Count) _
> > * * * * * * * *.End(xlToLeft).Column
> > * * * *For C = LastCol To 1 Step -1
> > * * * * * *If .Cells(1, C).Value = "delete" Then
> > * * * * * * * *If DeleteThese Is Nothing Then
> > * * * * * * * * * *Set DeleteThese = .Columns(C)
> > * * * * * * * *Else
> > * * * * * * * * * *Set DeleteThese = _
> > * * * * * * * * * * * *Application.Union(DeleteThese, .Columns(C))
> > * * * * * * * *End If
> > * * * * * *End If
> > * * * *Next C
> > * * * *If Not DeleteThese Is Nothing Then
> > * * * * * *DeleteThese.Delete
> > * * * *End If
> > * *End With
> >Next WS
> >End Sub

I inserted the code per JLGWhiz's instructions and inserted it into
the module. It worked great during a test. Now I am getting the same
response- nothing. When I step into the Macro the first line "Sub
DeleteColumns()" is highlighted in yellow. Is there a problem with
the name? Any feedback is always helpful. Thanks