Cleaning up columns macro

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I thought this would be easy but I haven't found much on this topic. I receive data dumps where I have to remove various columns of data as part of the reports that I have to produce. The particular columns that will need to be deleted don't always arrive in the same order. A typical example of column headings would be:
ID Name Age Position Status etc. What VBA coding would I need to delete Status and Age for example.
I regularly receive data sheets with over 30 column and have to delete at least half of them but they tend to be the same ones.
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
VBA Code:
Dim ray, i
ray = Array("Status", "Age")
On Error Resume Next
For Each i In ray
    Rows(1).Find(i).EntireColumn.Delete
Next
On Error GoTo 0
 
Last edited:
Upvote 0
Thanks footoo, that works nicely.
I do occasionally receive data with similar headings for example Position1, Position2 etc. I'm wondering whether arrays can accept wildcards?
 
Upvote 0
Try :
VBA Code:
Dim ray, i, cel As Range, rng As Range
ray = Array("Status", "Age", "Position")
Set rng = Range([A1], Cells(1, Columns.Count).End(1))
For Each i In ray
    For Each cel In rng
        If cel Like i & "*" Then cel.EntireColumn.Delete
    Next
Next
The following should be slightly quicker, but with only about 30 columns I doubt that the difference will be noticeable :
VBA Code:
Dim ray1, ray2, i, cel As Range, rng As Range
ray1 = Array("Status", "Age") 'for headings that can appear only once (exact matches only)
ray2 = Array("Position") 'for headings that may appear more than once (with or without suffixes)
On Error Resume Next
For Each i In ray1
    Rows(1).Find(i).EntireColumn.Delete
Next
On Error GoTo 0
Set rng = Range([A1], Cells(1, Columns.Count).End(1))
For Each i In ray2
    For Each cel In rng
        If cel Like i & "*" Then cel.EntireColumn.Delete
    Next
Next
 
Upvote 0
Thanks again footoo, it works in the end but it needed a couple of attempts. One of the files I get has 2 years data on a monthly basis ie 24 month. I tried the letter j assuming that it would delete Jan, Jun, July. It deleted most of them except the first Jul which it did delete when I ran the macro a second time. I'll try the second macro but I like the compact nature of that first macro.
 
Upvote 0
Tigerexcel

Do you have a list of columns you want to keep?
 
Upvote 0
Thanks again footoo, it works in the end but it needed a couple of attempts. One of the files I get has 2 years data on a monthly basis ie 24 month. I tried the letter j assuming that it would delete Jan, Jun, July. It deleted most of them except the first Jul which it did delete when I ran the macro a second time. I'll try the second macro but I like the compact nature of that first macro.
Probably because the macros need an amendment.
For both macros, instead of :
VBA Code:
For Each cel In rng
        If cel Like i & "*" Then cel.EntireColumn.Delete
Next
It should be :
VBA Code:
For x=rng.count to 1 Step -1
        If  rng(x) Like i & "*" Then rng(x).EntireColumn.Delete
Next

"but I like the compact nature of that first macro."
Compactness doesn't mean more efficient.
 
Upvote 0
Tigerexcel

Do you have a list of columns you want to keep?
I think that would be a better option. For example :
VBA Code:
Dim rng As Range, c%
Set rng = Range([A1], Cells(1, Columns.Count).End(1))
For c = rng.Count To 1 Step -1
    Select Case UCase(rng(c))
        Case "AA", "BB", "CC"
        Case Else: Columns(c).Delete
    End Select
Next
 
Upvote 0
Tigerexcel

Do you have a list of columns you want to keep?
Hi Norie,

For the most part, yes. That would probably work quite well although there would be quite a few columns to keep.
 
Upvote 0
Probably because the macros need an amendment.
For both macros, instead of :
VBA Code:
For Each cel In rng
        If cel Like i & "*" Then cel.EntireColumn.Delete
Next
It should be :
VBA Code:
For x=rng.count to 1 Step -1
        If  rng(x) Like i & "*" Then rng(x).EntireColumn.Delete
Next

"but I like the compact nature of that first macro."
Compactness doesn't mean more efficient.
That's good footoo, that did the trick but I don't get why the first version didn't work.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top