Hide/unhide empty rows and columns with VBA

[hjs1508]

In my Worksheet2, the data are generated based on formulas that reference to data in Worksheet1, and are formatted as a list (from A1 to AS100) like below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]...[/TD]
[TD]AS[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Code[/TD]
[TD]Property1[/TD]
[TD]Property2[/TD]
[TD]Property3[/TD]
[TD]Property4[/TD]
[TD]...[/TD]
[TD]Property28[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1[/TD]
[TD]AAA[/TD]
[TD]111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]CCC[/TD]
[TD][/TD]
[TD]222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]DDD[/TD]
[TD][/TD]
[TD]222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]99[/TD]
[TD]ZZZ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]999[/TD]
[/TR]
</tbody>[/TABLE]

Some rows and columns (except the header row) in this worksheet are empty, e.g. Row 3, Row 6, Column E, Column F.
I would like to auto hide and unhide both empty rows and columns, preferably with a button.
Any assistance or suggestions would be greatly appreciated. Thank you.

 

[Vbagreenhorn1]

try this:

Sub blabla()
    For i = 1 To 100
    
        If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True
        If Application.CountA(Columns(i)) = 1 Then Columns(i).Hidden = True
    Next
End Sub

 

[hjs1508]

Thanks Vbagreenhorn1!
Somehow the code works quite strangely.
In my actual worksheet2, there are only data from cell A1 to AS1(the headers) and A2 to N7, other cells are blank, i.e. ="" (based on the formula set).
After running the code, no rows are hidden, and columns hidden are P, S, V, Y, AB, AE, AH, AK, AN and AQ.

try this:

Sub blabla()
    For i = 1 To 100
    
        If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True
        If Application.CountA(Columns(i)) = 1 Then Columns(i).Hidden = True
    Next
End Sub

 

[SteveO59L]

Range("Q2").Select
ActiveCell.FormulaR1C1 = "=COUNTA(RC[-15]:RC[-2])"
Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q33"), Type:=xlFillDefault
Range("Q2:Q33").Select
ActiveWindow.SmallScroll Down:=6
Range("B40").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-38]C:R[-7]C)"
Range("B40").Select
Selection.AutoFill Destination:=Range("B40:O40"), Type:=xlFillDefault
Range("B40:O40").Select

Dim c As Range, rng
Set rng = Range("b4040")
For Each c In rng
If c.Value = 0 Then
c.EntireColumn.Hidden = True
End If
Next c
Set rng = Range("q2:q33")
For Each c In rng
If c.Value = 0 Then
c.EntireRow.Hidden = True
End If
Next c
Range("q2:q33").ClearContents
Range("b40:bo40").ClearContents

 

[ChrisCpH]

Hi Steve,

Would your routine be able to check, in a specified range, whether a cell in a specified row has a value of "0" and then autohide a row if that is true?

Thanks in advance
Chris