VBA: Delete All Columns (non-specific range) that are in between Column n and Column n

[Tash Point O]

Hello All,

Wracking my brain. I have a report that spits out a random amount of "nuisance" columns that I always have to delete as they are not important to my report.

The amount of these annoying columns can vary from 2-6 columns (usually), depending on the data. So I CAN'T built a macro saying delete columns U-AA, because again, the column range varies every time, and not all have headers/label.

What IS consistent is where these columns are wedged; in between 2 columns (Per pic below AA and AE) that have always have the same header (though the range of the right most column will vary as it depends on how many of these nuisance columns are created).

How can I make a macro that deletes all columns IN BETWEEN these 2 columns?

 

[Joe4]

Try this:

Sub MyMacro()

    Dim col1 As Long
    Dim col2 As Long
    
    On Error GoTo err_chk
    
'   Find column number of "UNITS%Complete"
    col1 = Cells.Find(What:="UNITS%Complete", After:=Range("A1"), LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
        
'   Find column number of "PrimaryAE"
    col2 = Cells.Find(What:="PrimaryAE", After:=Range("A1"), LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
        
'   Delete all columns in between
    If col2 - col1 > 1 Then
        Range(Cells(1, col1 + 1), Cells(1, col2 - 1)).EntireColumn.Delete
    Else
        MsgBox "No junk columns between the two values we are looking for"
    End If
        
    Exit Sub
    
    
err_chk:
    MsgBox "Cannot find the two values we are looking for"

End Sub

 

[Tash Point O]

Hello!

I see that you did there and I like it! Best of all, it worked!

At first it could not find the headers (nice error check btw) so I had to manually re-write the headers to see if your macro works and it did.

The header, when I click into it has a bunch of spaces in it before it has that strings Units Complete and Primary AE and I think that impedes the macro from working properly without my manual adjustment:

Am I missing something in basic Excel 101 (aside from TRIM function) of how to rid those spaces which I think VBA is reading as characters?

 

[Joe4]

It shouldn't matter if there are extra spaces before or after, because of this argument in the FIND function:

LookAt:=xlPart

That tells Excel that it does not need to match exactly. As long as that phase is contained in a cell, it should work.

If it is not working, I suspect that these really are not spaces, but are actually special characters (like non-breaking spaces). You find these quite commonly on data created from another source (imported report) or downloaded from the Web. We may need to clean them up first. But we need to first determine what it is that is actually in there.

Add this User Defined Function to your VBA code:

Function MyASCII(myString As String) As String
'   Converts a string to ASCII characters separated by dashes

    Dim i As Long
    Dim temp As String

    If Len(myString) > 0 Then
        For i = 1 To Len(myString)
            temp = temp & Asc(Mid(myString, i, 1)) & "-"
        Next i
        MyASCII = Left(temp, Len(temp) - 1)
    End If

End Function

Then, find the cells that have the values that we are looking for. Let's say that the first one is B13. Then enter this function anywhere on your worksheet and tell me what it returns:
=MyASCII(B13)

Repeat the process for the other value and tell me what that returns.

 

[Tash Point O]

ADVERTISEMENT

First - Thank you very much for working this through with me.
Second - I thought it might be /n (newlines) after I posted my reply
Third - Thanks for referencing the xlpart code - now I know about that and your explanations help me understand the bits of code

Here is what I see based on your function in the units%complete:

10-10-85-78-73-84-83-10-37-32-67-111-109-112-108-101-116-101 here is primary ae: 10-10-10-80-114-105-109-97-114-121-10-65-69 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[Joe4]

OK, it looks like you have some line feeds mixed in there. Is it all right if we remove them?

If so, just amend the code like this:

Sub MyMacro()

    Dim col1 As Long
    Dim col2 As Long
    
'   Remove line feeds
    On Error Resume Next
    Cells.Replace What:="" & Chr(10) & "", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    On Error GoTo 0
    
    On Error GoTo err_chk
    
'   Find column number of "UNITS%Complete"
    col1 = Cells.Find(What:="UNITS%Complete", After:=Range("A1"), LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
        
'   Find column number of "PrimaryAE"
    col2 = Cells.Find(What:="PrimaryAE", After:=Range("A1"), LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
        
'   Delete all columns in between
    If col2 - col1 > 1 Then
        Range(Cells(1, col1 + 1), Cells(1, col2 - 1)).EntireColumn.Delete
    Else
        MsgBox "No junk columns between the two values we are looking for"
    End If
        
    Exit Sub
    
    
err_chk:
    MsgBox "Cannot find the two values we are looking for"

End Sub

 

[Tash Point O]

Hello!

After the clean up code, the macro still couldn't find the column names .

I then did a few things but what worked was I changed Units%Complete to just, 'Complete'....and, that did the trick. Was the % tripping it up? Looks like it. Even thought it's in a string setting, VBA didn't like the %, not sure why and luckily the word Complete is only in that column.

Really appreciate your help with this creating this macro.

 

[Joe4]

You are welcome.
Glad you have it working the way you want now.