Convert script from looking for set text to a cell reference

[tazeo]

Using a script (partial below):
'CHECK WHETHER TO KEEP THE COLUMN
Select Case columnHeading
Case "Number", "Detailed Description", "Type", "Date", "Status"
'Do nothing

How can I change the code to be something like: Case A1,
swapping the text for a cell reference where the text is?

Thanks in advance

 

[Zot]

What is your objective? Multiple condition? Is it something like this?

Select Case Range("A1")
    Case "Number", "Detailed Description", "Type", "Date", "Status"

 

[tazeo]

What is your objective? Multiple condition? Is it something like this?

Select Case Range("A1")
    Case "Number", "Detailed Description", "Type", "Date", "Status"

Sorry didn't remember the VBA option. Looking for how I would do this:

Select Case columnHeading
     Case Sheet1!A1, Sheet1!A2, Sheet1!A4, Sheet1!A6, Sheet1!A9
'Do nothing

So rather than having text, having it identify the reference cell text.

Painful way I know, but the powers that be change the source titles every now and then.

 

[Zot]

Something like this?

Select Case True
     Case columnHeading = Sheet1!A1, _
            columnHeading = Sheet1!A2, _
            columnHeading = Sheet1!A4, _
            columnHeading = Sheet1!A6, _
            columnHeading = Sheet1!A9

 

[tazeo]

Thanks
Get an Object required error?

Here's the full code I am using:

Sub deleteIrrelevantColumns()
    Dim currentColumn As Integer
    Dim columnHeading As String
    
    Sheets("Paste").Select
    ActiveSheet.Columns("L").Delete

    For currentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1

        columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value

        'CHECK WHETHER TO KEEP THE COLUMN
        Select Case True            
            Case columnHeading = Instructions!AA9, _
            columnHeading = Instructions!AA10, _
            columnHeading = Instructions!AA11, _
            columnHeading = Instructions!AA12, _
            columnHeading = Instructions!AA13, _
            columnHeading = Instructions!AA14, _
            columnHeading = Instructions!AA15, _
            columnHeading = Instructions!AA16, _
            columnHeading = Instructions!AA17, _
            columnHeading = Instructions!AA18, _
            columnHeading = Instructions!AA19
                'Do nothing
            Case Else
                'Delete if the cell doesn't contain "Homer"
                If InStr(1, _
                   ActiveSheet.UsedRange.Cells(1, currentColumn).Value, _
                   "Homer", vbBinaryCompare) = 0 Then

                    ActiveSheet.Columns(currentColumn).Delete
                    End If
        End Select
    Next
    Sheets("Instructions").Select
End Sub

 

[Zot]

I think the error is here
columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value

Got 2 places like that

 

[Zot]

I think if not required, just use Long instead of Integer because the 64-bit system will convert internally to Long. Save micro seconds there

Avoid using Select but define sheet name as below. This way the program does not require sheet switching thus code run faster.

I try not to use UsedRange. If not mistaken, unless you save the file, the UsedRange will still use old value hen you delete rows or columns. Actual value will not show until you saved the file.

Instead of make program to calculate value multiple time whenever required such as in your code
ActiveSheet.UsedRange.Columns.Count (well you used only once here)

just calculate it once into a variable that you can use anytime like I did below. This will make code run faster.
nColumn = wsPaste.Cells(1, Columns.Count).End(xlToLeft).Column

Caution: Deleting column during run like this, you may end up skipping column because counter just count down without knowing that the column reference number has changed. Once column deleted, the reference to the remaining column might change.

The way I would write your code:

Sub deleteIrrelevantColumns()
    Dim currentColumn As Long, nColumn As Long
    Dim columnHeading As String
    Dim wsPaste As Worksheet, wsInst As Worksheet
    
    Set wsPaste = ActiveWorkbook.Sheets("Paste")
    Set wsInst = ActiveWorkbook.Sheets("Instructions")
    
'    Sheets("Paste").Select
    wsPaste.Columns("L").Delete
    
    nColumn = wsPaste.Cells(1, Columns.Count).End(xlToLeft).Column

    For currentColumn = nColumn To 1 Step -1

        columnHeading = ActiveSheet.Cells(1, currentColumn).Value

        'CHECK WHETHER TO KEEP THE COLUMN
        Select Case True
            Case columnHeading = Instructions!AA9, _
            columnHeading = Instructions!AA10, _
            columnHeading = Instructions!AA11, _
            columnHeading = Instructions!AA12, _
            columnHeading = Instructions!AA13, _
            columnHeading = Instructions!AA14, _
            columnHeading = Instructions!AA15, _
            columnHeading = Instructions!AA16, _
            columnHeading = Instructions!AA17, _
            columnHeading = Instructions!AA18, _
            columnHeading = Instructions!AA19
                'Do nothing
            Case Else
                'Delete if the cell doesn't contain "Homer"
                If Not wsPaste.Cells(1, currentColumn) = "Homer" Then
                    ActiveSheet.Columns(currentColumn).Delete
                End If
        End Select
    Next
'    Sheets("Instructions").Select
End Sub

 

[tazeo]

I think if not required, just use Long instead of Integer because the 64-bit system will convert internally to Long. Save micro seconds there

Avoid using Select but define sheet name as below. This way the program does not require sheet switching thus code run faster.

I try not to use UsedRange. If not mistaken, unless you save the file, the UsedRange will still use old value hen you delete rows or columns. Actual value will not show until you saved the file.

Instead of make program to calculate value multiple time whenever required such as in your code
ActiveSheet.UsedRange.Columns.Count (well you used only once here)

just calculate it once into a variable that you can use anytime like I did below. This will make code run faster.
nColumn = wsPaste.Cells(1, Columns.Count).End(xlToLeft).Column

Caution: Deleting column during run like this, you may end up skipping column because counter just count down without knowing that the column reference number has changed. Once column deleted, the reference to the remaining column might change.

The way I would write your code:

Sub deleteIrrelevantColumns()
    Dim currentColumn As Long, nColumn As Long
    Dim columnHeading As String
    Dim wsPaste As Worksheet, wsInst As Worksheet
   
    Set wsPaste = ActiveWorkbook.Sheets("Paste")
    Set wsInst = ActiveWorkbook.Sheets("Instructions")
   
'    Sheets("Paste").Select
    wsPaste.Columns("L").Delete
   
    nColumn = wsPaste.Cells(1, Columns.Count).End(xlToLeft).Column

    For currentColumn = nColumn To 1 Step -1

        columnHeading = ActiveSheet.Cells(1, currentColumn).Value

        'CHECK WHETHER TO KEEP THE COLUMN
        Select Case True
            Case columnHeading = Instructions!AA9, _
            columnHeading = Instructions!AA10, _
            columnHeading = Instructions!AA11, _
            columnHeading = Instructions!AA12, _
            columnHeading = Instructions!AA13, _
            columnHeading = Instructions!AA14, _
            columnHeading = Instructions!AA15, _
            columnHeading = Instructions!AA16, _
            columnHeading = Instructions!AA17, _
            columnHeading = Instructions!AA18, _
            columnHeading = Instructions!AA19
                'Do nothing
            Case Else
                'Delete if the cell doesn't contain "Homer"
                If Not wsPaste.Cells(1, currentColumn) = "Homer" Then
                    ActiveSheet.Columns(currentColumn).Delete
                End If
        End Select
    Next
'    Sheets("Instructions").Select
End Sub

Thanks
I can see what you mean, though I don't entirely understand it .

Still getting the Object Required Error, and it is just deleting column L each time I run the Macro, been months since I first started working on this, and I can't remember why the code had L in it, can't even find the original webpage I used as a guide.

Not sure whats going on.

If I were to go back to the beginning and ask how to delete all columns except the ones listed, is what I started with and you have tweaked (with my gratitude) the best option, or is there better ideas/approaches?

 

[Zot]

Which line is highlighted for error?

 

[tazeo]

Which line is highlighted for error?

So pressing F8 through the code, I get the error on the next press.

It also says runtime error 424 in the error message (when running from inside the editor).