CODEWITHSUNDEEP
excelvba
Pramod Pandit
Pramod Pandit

Reputation: 121

How to Hide the "Columns" if the Columns cells are empty within a certain range?

How to Hide the Columns if the Columns are empty within a certain range using VBA ? For eg: In below picture If the Columns cells are empty within range "A5:J22" then Hide those columns.Here, Columns E,F,H,I & J will be hidden.Column G will not be hidden as it has data on it on "G12".

enter image description here

Upvotes: 1

Views: 787

Answers (2)

Error 1004
Error 1004

Reputation: 8220

You could use:

  • Sum (use if you have numbers - is faster)

  • CountA (use if you have both numbers and texts - is slower)

    Sub test()

 Dim i As Long

 With ThisWorkbook.Worksheets("Sheet1")
     For i = 5 To 10
         If Application.WorksheetFunction.CountA(.Range(.Cells(5, i), .Cells(22, i))) > 0 Then
             .Columns(i).EntireColumn.Hidden = True
         Else
             .Columns(i).EntireColumn.Hidden = False
         End If
     Next i
 End With

End Sub

Upvotes: 2

MD Ismail Hosen
MD Ismail Hosen

Reputation: 118

Use this.

Public Sub HIDE()
Dim i As Long
For i = 1 To 10
    lastrow = Cells(1048576, i).End(xlUp).Row
    If lastrow < 5 Then
        Columns(i).Hidden = True
    End If
Next i
End Sub

Upvotes: 2

Related Questions