Delete Columns Macro

[cardan]

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.

 

[Chip Pearson]

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]

 

[cardan]

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]

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

 

[JLGWhiz]

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.

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]

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

 

[Chip Pearson]

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]

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]

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

 

[Javed]

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.

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]

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

 

[cardan]

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.

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]

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

 

[cardan]

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" <[email protected]> wrote in message

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

 

[cardan]

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]

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

 

[Chip Pearson]

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.

By default, yellow is the background color of the pending line of code
to run in the debugger, so VBA is just sitting there waiting for you
to F8 to go to the next line or F5 to let it run through.

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

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]

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]

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

 

[cardan]

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.

By default, yellow is the background color of the pending line of code
to run in the debugger, so VBA is just sitting there waiting for you
to F8 to go to the next line or F5 to let it run through.

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

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

I figured it out my issue. My formula was using capital letters and
the macro code did not account for capitals. (totally my fault per my
question). I did not know macros were case sensitive.

On another note, could I use this same formula for rows by replacing
the word Column with Row and "C"'s with"R"'s? I ask now because my
data set still may be too large and I may have to scale back some of
the rows. I would do it the same way- by writing a formula in the A
column and the have the macro search that and then delete. Thank you
again for all your help!

 

[JLGWhiz]

Try what you think will work, BUT, do it on a test file, not your original
file. The best way to learn VBA is by trial and error. When what you think
should work does not work, step through the code one line at a time to see
what it is doing. The tool tips feature of the VBE will show the values of
variables and objects when you mouse over them. Or you can use the locals
window to see all variables and their values and the immediate window to
view debug.print messages. There are plenty of safeguards built into VBA
that prevents you from breaking anything by trying different code. But like
I said, do it on a test file because you can wipe out a file if you write
bad code, and in some cases, it might not be recoverable. So when you text
code, make sure you have any workbooks closed that are not needed to test
the code.
After you have tried to do it yuurself and cannot get it to work. Post in
the NG for help.

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.

By default, yellow is the background color of the pending line of code
to run in the debugger, so VBA is just sitting there waiting for you
to F8 to go to the next line or F5 to let it run through.

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

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

I figured it out my issue. My formula was using capital letters and
the macro code did not account for capitals. (totally my fault per my
question). I did not know macros were case sensitive.

On another note, could I use this same formula for rows by replacing
the word Column with Row and "C"'s with"R"'s? I ask now because my
data set still may be too large and I may have to scale back some of
the rows. I would do it the same way- by writing a formula in the A
column and the have the macro search that and then delete. Thank you
again for all your help!