Delete Column Containing Data

Hey guys, new to the site, been searching for an answer but couldn't find exactly what I was looking for so I figured I'd ask. You all seem very knowledgeable.

What I'm trying to do is come up with a code to delete a whole column if it contains certain text. The text is always in Row 1.

How I am visualzing it in my mind is: If column contains text <insert text here> delete entire column.

I'm having trouble converting this visualization to code.

Anyone got any suggestions?

If the text may comprise only part of the cell in which it appears, you can use the VBA Find method:
Rows(1).Find(What:="bob", LookAt:=xlPart, ...).EntireColumn.DeleteIf the text is the entire contents of the cell, you can use Find or the worksheet Match function, which is also available to VBA:
Columns(WorksheetFunction.Match("bob", Rows(1), 0).Delete
You would need to wrap either one with some error handling (or error ignoring) in case it's not found.

Thank you for your response. I still can't get it to work :(

When I tried to use match it gave me an invalid qualifier error.

Here's my code.


Dim Col As Long, ColCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:

If Selection.Columns.Count > 1 Then
Set Rng = Selection
Else
Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))
End If
ColCnt = 0
For Col = Rng.Columns.Count To 1 Step -1
If Columns(WorksheetFunction.Matches("Suffix", Rows(1), 0).Delete) Then
End If
Next Col

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

To solely answer your immediate question and ignoring the rest of the code, you need to sort out your brackets and your worksheet function has a typo:

Columns(WorksheetFunction.Matches("Suffix", Rows(1), 0).Delete)


Change to:


If Columns(WorksheetFunction.Match("Suffix", Rows(1), 0)).Delete Then

Oh my goodness that fixed it :)

Thank you so much!

Also, is there an easy way to implement multiple data so that it will go through every column in the spreadsheet with the way the code is setup now?

Like say if I have multiple columns that need deleting, but they all have different headings.

Here's what I tried:

If Columns(WorksheetFunction.Match("DOB", "Gender", "Suffix", Rows(1), 0)).Delete Then

It gives me: "Wrong number of arguments or invalid property assignment."

If you look up the MATCH worksheet function in your Excel helpfile you will see that the syntax is like this:


Syntax

MATCH(lookup_value,lookup_array,match_type)

Lookup_value is the value you use to find the value you want in a table.


Lookup_value is the value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.


Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
If match_type is omitted, it is assumed to be 1.


You are trying to pass 5 arguments, hence the error...

My own personal preference would be to use the Range Object's Find Method.

I'm not really certain how I would implement that in the place of what I have. I'm still relatively new to this.

Would I simply be replacing the match worksheet function with the range object find method you suggested?

You can do it with the Match worksheet function. I think it's a little bit more tricky than using the Range Object's find method because you have to handle any errors that crop up when the header you're looking for isn't in the worksheet. That's why I said I personally would do it the other way.

If you want to check for different headers then you're going to have to use a loop. If there could be more than 1 column with each header then you're probably going to have to nest 2 loops. Are you familiar with Do... Loop and For... Next?

There's an example of using the find method in Post #2 in this thread. There's also an example in the VBA Help that illustrates how to loop find all occurances of a given value in a given range:

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

In your case, you expect each value to occur only once, but you want to look for several different values in turn. One possibility of automating this would be to provide a method that contains the repetitive code:

Private Sub deleteColumn(whichHeading As String)
'use .Find on the first row, delete the column containing the result
End Sub

'called so
Call deleteColumn("DOB")
Call deleteColumn("Gender")
Call deleteColumn("Suffix")

A good way to experiment with VBA is the macro recorder - record an action that you want to duplicate in your code, look at the produced macro, then use F1 to get more information on the individual commands.

Yea I'm not very familiar with setting up loops or anything like that. =\

Okay well Cas gave you a starter there so let's go for the Find method route, eh?

I did a quick google (http://www.ozgrid.com/forum/showpost.php?p=356248&postcount=6) and found this code (which I have slightly adapted). This shows you how to delete all columns which contain "My Header" in the first row. The code within the do... loop keeps executing until no more columns with the "My Header" header exist.


Sub Example()
Dim rngFound As Range
Dim sToFind As String
Dim wstTarget As Worksheet

Set wstTarget = Worksheets("Sheet1")

sToFind = "My Header"

Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sToFind, _
LookIn:=xlValues, _
lookat:=xlWhole)
If rngFound Is Nothing Then
Exit Do
Else
rngFound.EntireColumn.Delete
End If
Loop
End Sub

Notice the If rngFound Is Nothing Then line: this caters for the possibility that the header was not found.

The last step is that you want it to execute for different headers. There are a couple of ways you could do it: two of which have already been hinted at. Have a think about how you might try to do it! ;)

Thank you for your help guys! I got it to work. I did use the looping.

My last question would be, if I open multiple spreedsheets in excel, will I be able to make this run on all the spreadsheets I have open? I believe currently it only runs on the active spreadsheet and not the other ones that are open.

This is a sitation where I open about 50 spreadsheets into one instance of excel and make these changes. Will I just need to manuall run the macro for each spreadsheet?

Fantastic work jcleary47! :cool:

Please feel free to post your solution - others may benefit from it and there's even a chance we might be able to tweak it a little?!

Now that you've got the hang of loops, adapting your procedure to look at all worksheets in various workbooks should be no problem at all! For Each... Next loops are generally the best way to loop through collections, and in this case you will be interested in the Worksheets and Workbooks collections.

Regards,
Colin

Here is the final code I am using:



Sub BenetracSmasher()
Dim rngFound As Range
Dim sToFind As String
Dim wstTarget As Worksheet

Set wstTarget = Worksheets("Main")

sToFind = "Elig Group Qual 1"

Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sToFind, _
LookIn:=xlValues, _
lookat:=xlWhole)
If rngFound Is Nothing Then

Exit Do

Else
rngFound.EntireColumn.Delete

End If
Loop

sToFind = "Elig Group Qual 2"

Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sToFind, _
LookIn:=xlValues, _
lookat:=xlWhole)
If rngFound Is Nothing Then

Exit Do

Else
rngFound.EntireColumn.Delete

End If
Loop


sToFind = "Elig Group Qual 3"

Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sToFind, _
LookIn:=xlValues, _
lookat:=xlWhole)
If rngFound Is Nothing Then

Exit Do

Else
rngFound.EntireColumn.Delete

End If
Loop

sToFind = "Elig Group Qual 4"

Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sToFind, _
LookIn:=xlValues, _
lookat:=xlWhole)
If rngFound Is Nothing Then

Exit Do

Else
rngFound.EntireColumn.Delete

End If
Loop

sToFind = "Elig Group Qual 5"

Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sToFind, _
LookIn:=xlValues, _
lookat:=xlWhole)
If rngFound Is Nothing Then

Exit Do

Else
rngFound.EntireColumn.Delete

End If
Loop


sToFind = "BENEFIT QUAL 1"

Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sToFind, _
LookIn:=xlValues, _
lookat:=xlWhole)
If rngFound Is Nothing Then

Exit Do

Else
rngFound.EntireColumn.Delete

End If
Loop


sToFind = "Benefit Qual 2"

Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sToFind, _
LookIn:=xlValues, _
lookat:=xlWhole)
If rngFound Is Nothing Then

Exit Do

Else
rngFound.EntireColumn.Delete

End If
Loop

sToFind = "Benefit Qual 3"

Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sToFind, _
LookIn:=xlValues, _
lookat:=xlWhole)
If rngFound Is Nothing Then

Exit Do

Else
rngFound.EntireColumn.Delete

End If
Loop

sToFind = "Benefit Qual 4"

Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sToFind, _
LookIn:=xlValues, _
lookat:=xlWhole)
If rngFound Is Nothing Then

Exit Do

Else
rngFound.EntireColumn.Delete

End If
Loop

sToFind = "Benefit Qual 5"

Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sToFind, _
LookIn:=xlValues, _
lookat:=xlWhole)
If rngFound Is Nothing Then

Exit Do

Else
rngFound.EntireColumn.Delete

End If
Loop

End Sub



And this works great. It does exactly what I want it to do, just it only does it on the active spreadsheet/workbook and not the rest. I have to manually click through and run it for each one that is open.

iWorkbookCount = Application.Workbooks.Count


For iWorkbook = 1 To iWorkbookCount

Application.Workbooks(iWorkbook).Activate

Hi jcleary,

It's good to see the effort you've put in!

Now, before you start trying to loop through worksheets and workbooks, let's see if we can tidy up your code a little bit?

The code you posted is quite long and we can shorten it a little.
I'm going to assume that BENEFIT QUAL 1 can be in lower case. If it needs to be in upper case then we can address that.

Now, imagine that you had a hundred headers instead of ten. Would you quite separate Do... Loop structures for each of them? The code would get huge! What we can do is put the headers into the array and then loop through it. Like this:


Sub DeleteColumns()

Dim wstTarget As Worksheet
Dim rngFound As Range
Dim sarrToFind(9) As String, i As Integer

'Which headers do we want to delete?
For i = 0 To 4
sarrToFind(i) = "Elig Group Qual " & (i + 1)
sarrToFind(i + 5) = "Benefit Qual " & (i + 1)
Next i

'the worksheet we're going to work on is Sheet1
Set wstTarget = Worksheets("Sheet1")

'loop through each header we want to delete
For i = LBound(sarrToFind) To UBound(sarrToFind)

'loop while each header is found
Do
Set rngFound = wstTarget.Rows(1).Find( _
What:=sarrToFind(i), _
LookIn:=xlValues, _
lookat:=xlWhole)

If rngFound Is Nothing Then
Exit Do
Else
rngFound.EntireColumn.Delete
End If
Loop
Next i
End Sub


Now to understand this code you're going to have to do some background reading on arrays. Look up the LBound and UBound functions in your VBA helpfile. There's a good tutorial HERE (http://www.xtremevbtalk.com/showthread.php?t=68153) which discusses both looping and arrays.
Also try stepping through the code (using the F8 key) and watch how it works. While you're doing this keep an eye on your Locals window. Once you're comfortable with it, think about how you would introduce a couple of For Each... Next loops to loop through the worksheets and workbooks collections.

HTH!

Colin

Using the approach I suggested above, i.e. placing the .Find loop into a separate procedure and directly calling that repeatedly, simplifies the code somewhat, but at the cost of a tiny bit of duplication*
Public Sub DeleteColumns()

Dim i As Long

For i = 1 To 5
Call DeleteColumn(Worksheets("Sheet1"), "Elig Group Qual " & i)
Next i
For i = 1 To 5
Call DeleteColumn(Worksheets("Sheet1"), "Benefit Qual " & i)
Next i

End Sub

Public Sub DeleteColumn(wstTarget As Worksheet, sToFind As String)

Dim rngFound As Range

'loop while each header is found
Do
Set rngFound = wstTarget.Rows(1).Find(What:=sToFind, _
LookIn:=xlValues, _
LookAt:=xlWhole)

If rngFound Is Nothing Then
Exit Do
Else
rngFound.EntireColumn.Delete
End If
Loop

End Sub

It's a judgement call here which one you prefer, both arrays and subprocedures are indispensible tools that you should learn about. :)

There's a good tutorial HERE which discusses both looping and arrays.
And indentation! ;)

*duplication = repetition of code, such as the two occurances of "DeleteColumn(Worksheets("Sheet1"), ...)" in the example above. To be avoided as much as possible, because it makes code harder to read and changes more difficult to implement.

Thank you for all the great input guys. You are awesome.

I was originally thinking I could clean up the code an run a loop for the overall procedure, but I wasn't 100&#37; sure if doing it that way, it would catch multiple instances of a column heading. Often time these spreadsheets have the same column heading name in different columns repeating through the spreadsheet.

I also added a msgbox at the beginning asking if you really want to run the procedure with the option to cancel and one at the end that confirms it completed.

No worries... once you've done the background reading I suggested, let us know if you're still having trouble with looping through the worksheets and workbooks. :)

Awesome, I'm going to read up on the looping this weekend to get a better understanding for it.

There was one question about something I noticed.

In my code where it says:


Set wstTarget = Worksheets("Sheet1")


I understand that "Sheet1" is the name of the Sheet that it is editing in the workbooks. Now, all the of spreadsheets that I am running this macro on all have their data on Sheet1 and it is called Sheet1, but on occasion one will come in with a name of 'Main' or something else.

What would the procedure be for having it not care what the name of the sheet is, completely ignoring that all together?

That's no problem, as long as you have another way of identifying the sheet you want. If it's e.g. always the first sheet in the workbook, you can use "Worksheets(1)" instead of using a name.
We've actually been assuming you'd want to look at all the sheets in each book, in which case you'd (again) just use a loop, and the problem goes away altogether. :)