VBA to find a column label and delete that column

General Ledger · Jul 18, 2011

Dear All,

In a macro using Excel 2010, I am looking for code that finds certain column labels and deletes those columns.

Something like, find the column labels "Date of Birth" and "SSN" in row 1 and delete the entire columns. If a desired label does not exist, continue on to search for the next label. After all the labels have been searched for, simply end.

I have found some code to get me close but am not sure how to delete the correct column or if this is really the best approach.

Code:

For Each Word In Array("Date of Birth, "SSN")
Set aRange = Range("a1:iv1").Find(What:=Word, LookAt:=xlWhole, MatchCase:=False)
If aRange Is Not Nothing Then
?code to delete the entire column?
End If
Next Word

Thanks,

GL

Peter_SSs · Jul 18, 2011

Apart from the missing bit that you are asking about, some of your syntax is a bit mixed up. Try this in a copy of your workbook.

Code:

For Each Word In Array("Date of Birth", "SSN")
    Set aRange = Rows(1).Find(What:=Word, LookAt:=xlWhole, MatchCase:=False)
    If Not aRange Is Nothing Then
        aRange.EntireColumn.Delete
    End If
Next Word

General Ledger · Jul 18, 2011

Peter,

Your code no doubt solves my original problem. Now that I am trying to implement your solution, I came across an issue. My raw data has just a few columns to keep and many columns to delete. I would prefer to list the labels of the few columns to keep rather than the labels of the many columns to be deleted.

How can I adapt your code to keep only the columns of the entered words and delete the columns of any unmatching words? For example, if I wanted to delete every column except the Date of Birth and SSN column, what would the code be?

Thanks,

GL

Peter_SSs · Jul 18, 2011

I would use completely different code. If I wanted to keep columns with headers "H3", "H4" and "H7" then one way would be to use code like this. Note the comma before and after the list of headers to keep, and no extra spaces between the headers in that string.

 Sub Keep_Cols() Dim LC As Long, i As Long Const Hkeep As String = ",H3,H4,H7," LC = Cells(1, Columns.Count).End(xlToLeft).Column Application.ScreenUpdating = False For i = LC To 1 Step -1 If InStr(1, Hkeep, "," & Cells(1, i).Value & ",", _ vbTextCompare) = 0 Then Columns(i).Delete End If Next i Application.ScreenUpdating = True End Sub

General Ledger · Jul 18, 2011

Peter,

Your solution worked perfectly!!!

Thank you so much!!

GL

silverbolt · Jan 23, 2013

Thanks Peter for providing the solution. Sorry for reviving this old thread but I figured this is the quickest way to build on this.

Is there a way to deal with a much longer string? In the above example, we simply wanted to keep columns with headers "H3,H4,H7". What if the string length is towards 3000 characters? Is it even possible? Yes the headers I have are really long and have a few more columns I'd like to keep.

Thanks for all of your input!

Peter_SSs · Jan 24, 2013

silverbolt said:
Thanks Peter for providing the solution. Sorry for reviving this old thread but I figured this is the quickest way to build on this.

Is there a way to deal with a much longer string? In the above example, we simply wanted to keep columns with headers "H3,H4,H7". What if the string length is towards 3000 characters? Is it even possible? Yes the headers I have are really long and have a few more columns I'd like to keep.

Thanks for all of your input!

Welcome to the MrExcel board!

The previous code still works for me with headers of length 3,000. The problem you have is how to provide those long strings to the code.

1. Are the headers you want to keep stored somewhere in the workbook, or could they be?

2. Instead of the whole headers, are there shorter substrings in them that would still uniquely identify the column header?

silverbolt · Jan 25, 2013

Peter_SSs said:
Welcome to the MrExcel board!

The previous code still works for me with headers of length 3,000. The problem you have is how to provide those long strings to the code.

1. Are the headers you want to keep stored somewhere in the workbook, or could they be?

2. Instead of the whole headers, are there shorter substrings in them that would still uniquely identify the column header?

Thanks for the warm welcome Peter!

Yes unfortunately the columns that I want to keep has their names changing a bit, while the ones that are not to be kept have a fixed name but numerous.
There are substrings that I can identify with the columns that needs to be kept.
In the code above, would I be able to use wildcards?

For example, Const Hkeep As String = ",H3*,H4*,H7*," . Would that work out?

Thanks again.

Peter_SSs · Jan 25, 2013

Yes, you could use wildcards but I would change the structure of the code to do so.

I assume that with your suggestion ..
Const Hkeep As String = ",H3*,H4*,H7*,"
.. you were indicating that you would want to keep columns whose headings started with H3, H4 or H7

The code below should do that. The code generally has changed but also note the omission of the leading/trailing commas in the HKeep constant string

 Sub Keep_Cols() Dim LC As Long, i As Long, j As Long, NumKeeps As Long Dim Keeps Dim Found As Boolean Dim s As String Const HKeep As String = "H3*,H4*,H7*" Keeps = Split(HKeep, ",") NumKeeps = UBound(Keeps) + 1 LC = Cells(1, Columns.Count).End(xlToLeft).Column Application.ScreenUpdating = False For i = LC To 1 Step -1 s = Cells(1, i).Value j = 0 Found = False Do If s Like Keeps(j) Then Found = True j = j + 1 Loop Until Found Or j = NumKeeps If Not Found Then Columns(i).Delete Next i Application.ScreenUpdating = True End Sub 

If you wanted to keep columns that included H3, H4 or H7 anywhere in the title then change the HKeep line to

Const HKeep As String = "*H3*,*H4*,*H7*"

VBA to find a column label and delete that column

General Ledger

Active Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

General Ledger

Active Member

Peter_SSs

MrExcel MVP, Moderator

General Ledger

Active Member

silverbolt

New Member

Peter_SSs

MrExcel MVP, Moderator

silverbolt

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

VBA to find a column label and delete that column

Active Member

Excel Facts

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock