On May 7, 4:22*am, Javed wrote:
On May 7, 6:01*am, "JLGWhiz" 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 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 ToolsMacroNacros. Then click
the macro name and click the run button.
"cardan" wrote in message
....
On May 6, 3:47 pm, Chip Pearson 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
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!