Skip to content

Instantly share code, notes, and snippets.

Created March 29, 2023 12:25
Show Gist options
  • Save bskaggs/28580431b26ae69212595af73847efa1 to your computer and use it in GitHub Desktop.
Save bskaggs/28580431b26ae69212595af73847efa1 to your computer and use it in GitHub Desktop.
ChatGPT-4 generated example of how to remove all but two columns from a spreadsheet
To create a macro in Excel for Mac that removes all columns except "Foo Bar" and "Baz Biz", unmerges all cells, and adds a filter to the "Baz Biz" column, you can use the following VBA code:
1. Press F11 or click Tools > Visual Basic to open the Visual Basic for Applications editor.
2. Click Insert > Module to insert a new module.
3. Copy and paste the following VBA code into the module:
Sub FilterAndUnmerge()
Dim ws As Worksheet
Dim lastColumn As Long
Dim currentColumn As Long
Dim fooBarCol As Long
Dim bazBizCol As Long
Set ws = ActiveSheet
lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
' Find Foo Bar and Baz Biz columns
For currentColumn = 1 To lastColumn
If ws.Cells(1, currentColumn).Value = "Foo Bar" Then
fooBarCol = currentColumn
ElseIf ws.Cells(1, currentColumn).Value = "Baz Biz" Then
bazBizCol = currentColumn
End If
Next currentColumn
' Check if columns were found
If fooBarCol = 0 Or bazBizCol = 0 Then
MsgBox "Foo Bar or Baz Biz column not found."
Exit Sub
End If
' Delete other columns
For currentColumn = lastColumn To 1 Step -1
If currentColumn <> fooBarCol And currentColumn <> bazBizCol Then
End If
Next currentColumn
' Unmerge all cells in the worksheet
' Add filter to the "Baz Biz" column
ws.Rows(1).AutoFilter Field:=2, Criteria1:="<>"
Application.ScreenUpdating = True
4. Close the VBA editor.
5. To run the macro, press `F8` or click `Run` > `Run Sub/UserForm` in the VBA editor.
This macro will remove all columns except "Foo Bar" and "Baz Biz", unmerge all cells in the sheet, and add a filter to the "Baz Biz" column. Make sure to save your Excel workbook as a macro-enabled workbook (.xlsm) to keep the macro for future use.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment