Hide Column If Usedrange of Column ISBLANK
 

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

Hide Column If Usedrange of Column ISBLANK
 

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

skrev i meddelelsen
...
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

Hide Column If Usedrange of Column ISBLANK
 

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

wrote:

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

--

Dave Peterson

Hide Column If Usedrange of Column ISBLANK
 

On Apr 12, 12:30*pm, "Per Jessen" wrote:
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

skrev i ...



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- Hide quoted text -

- 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

Hide Column If Usedrange of Column ISBLANK
 

On Apr 12, 1:03*pm, Dave Peterson wrote:
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

wrote:

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

--

Dave Peterson

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