CODEWITHSUNDEEP
vbaexcel
James
James

Reputation: 499

Delete Blank Columns starting at Row 2

I am trying to delete blank columns in the worksheet from Row 2 . If the whole column is blank i want to delete the column.

The reason i am starting from row 2 is because i have headers in row 1. Subsequently these will also be deleted if that column is blank. How do i start from row 2?

Sub Delete_Cols()
 Dim MyRange As Range
    Dim iCounter As Long
    Set MyRange = ActiveSheet.UsedRange
    For iCounter = MyRange.Columns.Count To 1 Step -1
       If Application.CountA(Columns(iCounter).EntireColumn) = 0 Then
       Columns(iCounter).Delete
       End If
    Next iCounter
End Sub

I need the macro to start checking for blank columns from row 2.

Upvotes: 0

Views: 1015

Answers (3)

Storax
Storax

Reputation: 12167

If I got you right you would like to see if the column starting with row 2 is empty and if yes, yoo would like to delete the column. If you have a header in every column your code only needs a small correction

Sub Delete_Cols()
    Dim MyRange As Range
    Dim iCounter As Long
    Set MyRange = ActiveSheet.UsedRange
    For iCounter = MyRange.Columns.Count To 1 Step -1
        If Application.CountA(Columns(iCounter).EntireColumn) = 1 Then
            Columns(iCounter).Delete
        End If
    Next iCounter
End Sub

Upvotes: 1

real_yggdrasil
real_yggdrasil

Reputation: 1263

The VBA below deletes a column in your sheet if it is empty:

Sub DeleteEmptyColumns()
  'Updateby20140317
  Dim rng As Range
  Dim InputRng As Range
  xTitleId = "KutoolsforExcel"
  Set InputRng = Application.Selection
  Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
  Application.ScreenUpdating = False
  For i = InputRng.Columns.Count To 1 Step -1
      Set rng = InputRng.Cells(1, i).EntireColumn
      If Application.WorksheetFunction.CountA(rng) = 0 Then
        rng.Delete
    End If
  Next
  Application.ScreenUpdating = True
End Sub

Upvotes: 0

Vityata
Vityata

Reputation: 43585

You need to Clear and not to Delete, if you want to start from Row 2. This is a way to go around it:

If Application.CountA(Columns(iCounter).EntireColumn) = 0 Then
    Range(Cells(2, iCounter), Cells(2 ^ 16, iCounter)).Clear
End If

It will clear the ranges from row 2 to row 65536. Probably it will work the way you want it.

Upvotes: 0

Related Questions