Hide Column If Usedrange of Column ISBLANK

[whasupwityou]

Hi guys,

I've searched and tried to reuse some of the code to fit my needs but,
so far I've not been successful. So, my last resort is to ask for
your assistance. I'm trying to Loop through Columns E:AI's used
range, beginning with row 12. Number of rows could be from 1 to 5000
rows. I need to hide columns that have blanks in all rows (12 to ?)
for that column. Appreciate your assistance and thank you in
advance. Ron

 

[Per Jessen]

Hi Ron

Try this:

Sub HideEmptyRows()
StartRow = 12
For Each col In Columns("E:AI")
ColAdr = col.Address
TargetCol = IIf(Mid(col.Address, 4, 1) = "$", _
Mid(col.Address, 2, 1), Mid(col.Address, 2, 2))
LastRow = Range(TargetCol & Rows.Count).End(xlUp).Row
RowCount = Range(TargetCol & 12, TargetCol & LastRow).Rows.Count
EmptyRows = WorksheetFunction.CountBlank _
(Range(TargetCol & 12, TargetCol & LastRow))
If RowCount = EmptyRows Then Columns(TargetCol).Hidden = True
Next
End Sub

Regards,
Per

 

[Dave Peterson]

Another one:

Option Explicit
Sub testme()
Dim myCol As Long

Dim LastRow As Long

With Worksheets("Sheet1") '<-- change this
'last row of the used range
With .UsedRange
LastRow = .Rows(.Rows.Count).Row
End With
If LastRow < 12 Then
MsgBox "nothing in row 12 or later"
Exit Sub
End If

For myCol = .Range("e1").Column To .Range("ai1").Column
If Application.CountA(.Range(.Cells(12, myCol), _
.Cells(LastRow, myCol))) = 0 Then
.Columns(myCol).Hidden = True
End If
Next myCol
End With

End Sub

 

[whasupwityou]

Hi Ron

Try this:

Sub HideEmptyRows()
StartRow = 12
For Each col In Columns("E:AI")
    ColAdr = col.Address
    TargetCol = IIf(Mid(col.Address, 4, 1) = "$", _
        Mid(col.Address, 2, 1), Mid(col.Address, 2, 2))
    LastRow = Range(TargetCol & Rows.Count).End(xlUp).Row
    RowCount = Range(TargetCol & 12, TargetCol & LastRow).Rows.Count
    EmptyRows = WorksheetFunction.CountBlank _
        (Range(TargetCol & 12, TargetCol & LastRow))
    If RowCount = EmptyRows Then Columns(TargetCol).Hidden = True
Next
End Sub

Regards,
Per

<[email protected]> skrev i meddelelsen




- Show quoted text -

Hi Per Jessen... thank you for your assistance. My mistake.
Apparently there is something in the cells I'm considering blank. I'm
looking to hide the columns if there are no user input of text or
numeric. Your code does not work in the target worksheet but, does
work when I paste the data to another worksheet and run your code.
So, I know your code works, my sheet's cells must not be blank. Can
your code be edited to do that? Thank you for your assistance. Ron

 

[whasupwityou]

Another one:

Option Explicit
Sub testme()
    Dim myCol As Long

    Dim LastRow As Long

    With Worksheets("Sheet1") '<-- change this
        'last row of the used range
        With .UsedRange
            LastRow = .Rows(.Rows.Count).Row
        End With
        If LastRow < 12 Then
            MsgBox "nothing in row 12 or later"
            Exit Sub
        End If

        For myCol = .Range("e1").Column To .Range("ai1").Column
            If Application.CountA(.Range(.Cells(12, myCol), _
                                        .Cells(LastRow, myCol))) = 0 Then
                .Columns(myCol).Hidden = True
            End If
        Next myCol
    End With

End Sub

Hi Dave,
Worked like it was supposed to. I really appreciate yours and
everyone else assistance that posts solutions to this site. The site
is so through that I usually don't have to ask. Ron