How can I use VBA to delete all columns which are empty apart from a specific header?

Question

I'd like to delete all columns in a worksheet which meet the following criteria:

row 1 = "foobar"
rows 2-1000 are empty

It sounds simple enough but I haven't managed to get it working fully. Any help would be massively appreciated.

Thanks!

"but I haven't managed to get it working fully" - please edit your post with the code you've tried. Then we can help you fix what it isn't working. — Scott Holtzman, May 22 '12 at 16:05
If your data has headers then you can use Autofilter to delete blank cells. — Siddharth Rout, May 22 '12 at 16:40

score 4 · Answer 1 · edited Jan 07 '22 at 05:50

Fastest way to delete rows as per your requirement.

I am assuming that Row1 Has Column Headers

Option Explicit

Sub Sample()
    Dim aCell As Range, rng As Range
    Dim LastCol As Long, LastRow As Long, i As Long
    
    With Sheets("Sheet1")
        Set aCell = .Rows(2).Find(What:="foobar", LookIn:=xlValues, _
        Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        If Not aCell Is Nothing Then .Rows(2).Delete
        
        LastRow = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).Row
        
        LastCol = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByColumns, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).Column
        
        Set rng = Range("A1:" & Split(Cells(, LastCol).Address, "$")(1) _
                  & LastRow)
        
        ActiveSheet.AutoFilterMode = False
    
        For i = 1 To LastCol
            rng.AutoFilter Field:=i, Criteria1:=""
        Next i
        
        rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        
        ActiveSheet.AutoFilterMode = False
    End With
End Sub

Sorry for not responding to this. I was looking to delete columns, not rows--is that what the above will do? Thank you. — seegoon, Jun 19 '12 at 11:16
@seegoon: I can amend the above code to delete columns as well :) Can you show me a snapshot of how your data looks? It's just a one line change I guess. If I can see a snapshot then I can check it for you... — Siddharth Rout, Jun 19 '12 at 11:19

score 3 · Accepted Answer · edited Jun 19 '12 at 11:22

3

How about

dim col as Long, lastCol as Long, r as range
lastCol = ActiveSheet.Usedrange.columns(Activesheet.Usedrange.columns.count).column
for c=lastCol to 1 Step -1
    set r = Range(Cells(1, c), Cells(1000, c))
    if r.Rows(1) = "foobar" Then
        if WorksheetFunction.CountA(Range(r.Rows(2), r.Rows(r.Rows.Count))) = 0 then
            Columns(c).delete
        end if
    end If
next

[edit by OP: added a missing space]

edited Jun 19 '12 at 11:22

seegoon

563
1
8
15

answered May 22 '12 at 16:29

Alex K.

171,639
30
264
288

while your solution is good, it doesn't account for cells that appear to be blank but are not, because they have formats, comments, or a formula that results to zero... see this page for more information http://www.ozgrid.com/forum/showthread.php?t=26509 – Scott Holtzman May 22 '12 at 16:31
The OP did say "empty", so I would have suggested something similar to this. – Jerry Beaucaire May 22 '12 at 22:27
The above works fantastically for my purpose--thank you! Small amendment to the fifth line, though: `ifr.Rows(1)` should be `if r.Rows(1)`--a space was missing. – seegoon Jun 19 '12 at 11:20

How can I use VBA to delete all columns which are empty apart from a specific header?

2 Answers2

Linked