hide columns if they are empty populated from userform

**KK1234** · 04-07-2014, 06:56 AM

Hi all,
Following code works fine. It will hide column if row 2 is empty ( But only for data in row 2 of the respective columns)
How do I get it it work for the entire columns?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("J2").Value = "" Then
        Columns("J").EntireColumn.Hidden = True
    Else
        Columns("J").EntireColumn.Hidden = False
    End If
If Range("H2").Value = "" Then
        Columns("H").EntireColumn.Hidden = True
    Else
        Columns("H").EntireColumn.Hidden = False
    End If
If Range("I2").Value = "" Then
        Columns("I").EntireColumn.Hidden = True
    Else
        Columns("I").EntireColumn.Hidden = False
    End If
If Range("K2").Value = "" Then
        Columns("K").EntireColumn.Hidden = True
    Else
        Columns("K").EntireColumn.Hidden = False
    End If

If Range("L2").Value = "" Then
        Columns("L").EntireColumn.Hidden = True
    Else
        Columns("L").EntireColumn.Hidden = False
    End If

If Range("M2").Value = "" Then
        Columns("M").EntireColumn.Hidden = True
    Else
        Columns("M").EntireColumn.Hidden = False
    End If
If Range("F2").Value = "" Then
        Columns("F").EntireColumn.Hidden = True
    Else
        Columns("F").EntireColumn.Hidden = False
    End If
If Range("G2").Value = "" Then
        Columns("G").EntireColumn.Hidden = True
    Else
        Columns("G").EntireColumn.Hidden = False
    End If
If Range("N2").Value = "" Then
        Columns("N").EntireColumn.Hidden = True
    Else
        Columns("N").EntireColumn.Hidden = False
    End If


End Sub

Test file is attached
Need some help please
Kind regards

**MarvinP** · 04-07-2014, 07:19 AM

Hi KK,
Try this code instead of yours.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ColCtr As Double
    For ColCtr = 6 To 14
        If Cells(Rows.Count, ColCtr).End(xlUp).Row = 1 Then
            Columns(ColCtr).EntireColumn.Hidden = True
        Else
            Columns(ColCtr).EntireColumn.Hidden = False
        End If
    Next ColCtr
End Sub

**MarvinP** · 04-07-2014, 07:32 AM

If you don't want to count from 6 to 14 and use column letters then this will work also.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ColCtr As Double
    For ColCtr = Cells(, "F").Column To Cells(, "N").Column
        If Cells(Rows.Count, ColCtr).End(xlUp).Row = 1 Then
            Columns(ColCtr).EntireColumn.Hidden = True
        Else
            Columns(ColCtr).EntireColumn.Hidden = False
        End If
    Next ColCtr
End Sub

**KK1234** · 04-07-2014, 08:08 AM

Hi MarvinP,
Great work.
I have understood your code and it works fine.
Really appriciated your help.
Many thanks
Kind regards

**KK1234** · 04-07-2014, 08:10 AM

Hi again,
I have two more questions, however as per forum rule they will have to be in another Thread.
See you soon
Kind regards

hide columns if they are empty populated from userform

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

hide columns if they are empty populated from userform

Re: hide columns if they are empty populated from userform

Re: hide columns if they are empty populated from userform

Re: hide columns if they are empty populated from userform

Re: hide columns if they are empty populated from userform

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Hide empty columns Toggle

[SOLVED] Hide empty rows of shown columns

VBA code to hide empty columns.

[SOLVED] Un hide non empty columns

Un hide non empty columns

Bookmarks

Bookmarks

Posting Permissions