Let’s face it. We all need a little help to make our lives easier, especially when it comes to work. Like many others, I hopped on the AI bandwagon to see how it can truly help me with my work, and I got the first taste of success when I used it to create a VBA code that hides all columns based on certain condition. However, I’ll admit, it wasn’t a smooth ride right from the start!
So, here’s the situation… the Excel sheet contains more than 70 columns. Starting from column C, each column contains a language, e.g., “Arabic ar”, “English (BE) en-be”, etc. I want to hide all columns where the language is English, and it starts with “en-“.
This was my initial prompt:
Write a VBA code that I can use in my Excel sheet to solve this:
I want to hide all columns where there is a text that begins with en-
This was the first result:
Sub HideColumns()
Dim lastColumn As Integer
Dim cell As Range
‘ Define the last column in the worksheet
lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
‘ Loop through each column from right to left
For i = lastColumn To 1 Step -1
‘ Check if the first cell in the column starts with “en-“
Set cell = Cells(1, i)
If Left(cell.Value, 3) = “en-” Then
‘ Hide the column if the condition is met
Columns(i).Hidden = True
End If
Next i
End Sub
Here’s how to use the code:
- Open your Excel sheet.
- Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
- Insert a new module by clicking Insert > Module.
- Copy and paste the code into the module.
- Close the VBA editor.
- Press ALT + F8 to open the macro dialog box.
- Select the HideColumns macro from the list and click Run.
- All columns that have a cell starting with “en-” will be hidden.
Please note that this code will only check the first cell in each column. If you have headers in the first row and want to check a different row, you can modify the code accordingly.
Without reading the entire instruction, I happily copied the code and clicked the magic button to run it, but nothing happened! Well, that was because, Mr. AI already explained that the code will only check the first cell in each column.
So obviously it didn’t work, because the first cell in each of the column in my sheet was empty:
Fine. Round two…
I want to hide all columns where there is a text that begins with en-, and the code should start checking from row 3 onwards.
The results:
Sub HideColumns()
Dim lastColumn As Integer
Dim cell As Range
‘ Define the last column in the worksheet
lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
‘ Loop through each column from right to left
For i = lastColumn To 1 Step -1
‘ Check if any cell in the column, starting from row 3, starts with “en-“
For Each cell In Range(Cells(3, i), Cells(Rows.Count, i).End(xlUp))
If Left(cell.Value, 3) = “en-” Then
‘ Hide the column if the condition is met
Columns(i).Hidden = True
Exit For ‘ Exit the loop if a match is found in the column
End If
Next cell
Next i
End Sub
Nothing. Happened.
Now, Mr. AI cannot be more stupid than the undisputed champion – me – so something wasn’t right with my prompt. After repeatedly reading my prompt, I realized where I went wrong.
I want to hide all columns where there is a text that begins with en-,…
No wonder it didn’t work. None of the columns begin with en-:
Okay, third time’s the charm…it gotta be, it gotta be:
I want to hide all columns where a cell contains the text “en-“, and the code should start checking in row 3.
The results:
Sub HideColumnsContainingText()
Dim lastColumn As Long
Dim cell As Range
‘ Get the last column in the active sheet
lastColumn = Cells(3, Columns.Count).End(xlToLeft).Column
‘ Loop through each column starting from column 1 to the last column
For Each cell In Range(Cells(3, 1), Cells(3, lastColumn))
‘ Check if the cell’s value contains the text “en-“
If InStr(1, cell.Value, “en-“, vbTextCompare) > 0 Then
‘ Hide the entire column
Columns(cell.Column).Hidden = True
End If
Next cell
End Sub
I threw my clenched fist in the air and screamed YES!!! The code finally worked! The difference this time, was that, instead of using “begins with”, I replaced it with “contains”:
I want to hide all columns where a cell contains the text “en-“
So, the magic word here was “contains”. The code will look for any column (starting from row 3, of course) that has “en-“ in it – regardless of whether it starts at the beginning or at the end of a sentence – and then hides those columns.
I hope you will find this useful for you too. Remember, get your prompt right and you will get the right outcome!
