VBA macro to auto hide rows and columns with no value

[jbillyo]

Apologies if this has already been posted, I'm new to this forum but I have had a search but couldn't find anything specific enough.

I have found a code for hiding rows which works well and I've tried to adapt it to work for columns too.

This is what I have tried:

Option Explicit
Private Sub Worksheet_Activate()
Dim r As Range, c As Range
Set r = Range("a9:b39")
Application.ScreenUpdating = False
For Each c In r
If Len(c.Text) = 0 Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
Application.ScreenUpdating = True
End Sub
Option Explicit
Private Sub Worksheet_Activate()
Dim r As Range, c As Range
Set r = Range("f6:ao6")
Application.ScreenUpdating = False
For Each c In r
If Len(c.Text) = 0 Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False
End If
Next c
Application.ScreenUpdating = True
End Sub


I am also trying to get the simple average of rows/columns not to include blank cells.

Any help would be gratefully appreciated.

Cheers
John

 

[Michael M]

Using the Average function doesn't include blanks in the equation....so there shouldn't be a problem.....unless of course they aren't really blank !!

If ALL the blank rows are hidden there shouldn't be ANY blank Columns....!!!

Try this

Sub MM1()
Dim RB As Range
On Error Resume Next
Set RB = ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks)
If Not RB Is Nothing Then RB.EntireRow.Hidden = True
End Sub

 

[jbillyo]

Sorry, the note on the end isn't right. It is a countif that gives me a percentage.
=COUNTIF(F9:AO9,"")/$D$3

Thanks in advance

 

[jbillyo]

Thank you Michael, I have tried the code and it works for the rows because they are blank but the data in the columns is sometimes needed if cells aren't blank

 

[Michael M]

Okay well you can't hide part of a row OR part of a column !!

 

[jbillyo]

Thanks again Michael
Is it possible to autohide a complete column depending on a whether a particular cell is blank?

f6:ao6 is the range of cells that might appear blank.​

John

 

[Michael M]

Maybe this then

Sub MM1()
Dim c As Integer
For c = 41 To 6 Step -1
If Cells(6, c).Value = "" Then Columns(c).EntireColumn.Hidden = True
Next c
End Sub

 

[jbillyo]

Thank you so much Michael, seems to have done the trick. Maybe one day, I will understand how!!
Thanks for your time

 

[Michael M]

Glad to help...