Delete Blank or zero columns in a range

Question

This is what I need: Range say columns F:F, G:G, K:K. Delete the entire column if the cells are zero or blank only for these specific columns. No action if there is any other value in it.

Given below is what I am using now. But this deletes other columns that should not be deleted.

LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LastColumn = Sheets("Sheet1").Cells(9, Columns.Count).End(xlToLeft).Column
lastcol_name_1 = Replace(Cells(1, LastColumn).Address(False, False), "1", "") 

'to find letter of last column

With Sheets("Sheet1")         
    For i = 1 To LastColumn
        lastcol_name_1 = Replace(Cells(1, i).Address(False, False), "1", "") 'to find letter of last column
        If(lastcol_name_1=“K” or lastcol_name_1=”L” or lastcol_name_1=”M”) then ‘write the column name here
            If Evaluate("sum(countif(" & .Range(.Cells(10, i), _
                .Cells(LastRow, i)).Address & ",{"""",0}))") = LastRow - 10 + 1 Then _
                .Columns(i).Delete
        Endif
    Next i

Also instead of creating and parsing a string why not just use If i >=11 And i <=13 then — Scott Craner
@ScottCraner That would certainly work, though in practice I've found using a range object and adding ranges to it via union then calling a delete at the end to always be faster. Would you agree or is this a glaring oversight on my part? — Zerk
@Zerk nope it is always faster to do one delete over many, but as the OP is only deleting three columns at most the difference is probably not worth the effort. — Scott Craner

Amorpheuses Amorpheuses · Accepted Answer · 2017-02-11T07:07:32

If I'm understanding you correctly the following should delete columns "F", "G" and "K" if they are empty and shift the remaining columns to the left - assuming that's what you want to do here. I've left as much of your own code in place as I could:

Sub main():

  LastColumn = Sheets("Sheet1").Cells(9, Columns.Count).End(xlToLeft).Column
  lastcol_name_1 = Replace(Cells(1, LastColumn).Address(False, False), "1", "")

  For i = LastColumn To 1 Step -1
    lastcol_name_1 = Replace(Cells(1, i).Address(False, False), "1", "")
    If (lastcol_name_1 = "F" Or lastcol_name_1 = "G" Or lastcol_name_1 = "K") Then
      If Application.CountA(Columns(i).EntireColumn) = 0 Then
        Columns(i).Delete Shift:=xlToLeft
      End If
    End If
  Next i

End Sub

Delete Blank or zero columns in a range

1 Answers