Hide a row when detect any value in a row

[francozzy]

Hi ,

I would like to hide some row that doesn't have any value for each cell within a row,

here below sample table :

Items / Stocks	Nov	Dec	Jan	Feb
Office Desk	3 pcs	6 pcs		10 pcs
Office Chair	20 pcs	40 pcs	10 pcs	80 pcs
Printer
fax machine		3 pcs
filling cabinet

<tbody>
</tbody>

based on the table, i would like to hide row for "Printer" and "FIlling Cabinet", that doesn't have any values on Stocks
so what i have to do to make this happen ?



Thank you

 

[francozzy]

I already have this code below,
but as far as i know that the code below only can be use for match the content specific value with defined parameter
and not even scanning all cell within a row as one row, to collect the info that if blank within a row, the current row will be hide

        Dim shName As String
        Dim cH As String
        Dim colName As String
        Dim c As Range
       
        For Each c In ThisWorkbook.Sheets(shName).Range((cH & colName) & ":" & cH & LastRow)
            'Debug.Print c
            'Exit Sub
            If c.Value <> "2 Pcs" Then
                c.EntireRow.Hidden = True
                Else
                c.EntireRow.Hidden = False
            End If
            
        Next c

so what i have to do ?

 

[Peter_SSs]

Give this a try in a copy of your workbook.

Sub Hide_Rows()
  Dim nc As Long, lr As Long
  
  nc = Cells(1, Columns.Count).End(xlToLeft).Column + 1
  lr = Cells(Rows.Count, 1).End(xlUp).Row
  Application.ScreenUpdating = False
  With Cells(1, nc).Resize(lr)
    .FormulaR1C1 = "=if(countif(RC2:RC[-1],""?*""),"""",1)"
    On Error Resume Next
    .SpecialCells(xlFormulas, xlNumbers).EntireRow.Hidden = True
    On Error GoTo 0
    .EntireColumn.Delete
  End With
  Application.ScreenUpdating = True
End Sub

 

[francozzy]

Hi Peter,

Thank you for response

I'm sorry for my late response, i'm still newbie at VBA.
I'm still little bit confused with your code and formula.
Could you explain to me more about the code ?
Is there any value of parameter that i can change, so i can adapt to my own table ?
Cause i would like to able change number of row header table or side column.

I wait your response as soon as possible

Thank You.

 

[Peter_SSs]

I assume that the code did not work correctly with your data/layout?

With no other information provided, I assumed that your table started in cell A1, and that the table was the only thing on the worksheet.
If that is not your circumstance, could you provide more details so we have an understanding of what you do actually have and where it is?

 

[My Aswer Is This]

Assuming your image here is showing column A to E
If that is not true then give me more specifics:

Try this:

Sub Count_Me()
'Modified 5/28/18 7:10 AM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim ans as  Long
Dim Lastrow As Long
Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To Lastrow
        ans = Application.WorksheetFunction.CountA(Cells(i, 2).Resize(, LastColumn - 1))
            If ans = 0 Then Rows(i).Hidden = True
    Next
Application.ScreenUpdating = True
End Sub

 

[francozzy]

I assume that the code did not work correctly with your data/layout?

With no other information provided, I assumed that your table started in cell A1, and that the table was the only thing on the worksheet.
If that is not your circumstance, could you provide more details so we have an understanding of what you do actually have and where it is?

Hi Peter,

Yup, i need for dynamically change between amount number of row/column table header and sidebar legend
btw, thank you

Assuming your image here is showing column A to E
If that is not true then give me more specifics:

Try this:

Sub Count_Me()
'Modified 5/28/18 7:10 AM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim ans as  Long
Dim Lastrow As Long
Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To Lastrow
        ans = Application.WorksheetFunction.CountA(Cells(i, 2).Resize(, LastColumn - 1))
            If ans = 0 Then Rows(i).Hidden = True
    Next
Application.ScreenUpdating = True
End Sub

Many Thanks,

By looking your code ,
I think, i can figure it out how to make it more dynamically.
It would be depend on two parameter on your code

first parameter is parameter i

[I]    For[B] i = 2[/B] To Lastrow [/I]

second parameter is parameter that represent by 2

[I] Application.WorksheetFunction.CountA([B]Cells(i, 2)[/B].Resize(, LastColumn - 1)) [/I]

It's Great

 

[My Aswer Is This]

Well glad we were able to give you some help even it is something you will have to modify.

Your image did not tell us what columns we were working with so I just assume we started with column A

If we are dealing with a Table we always need the table name.

Table ranges could be column G to K or J to xx
So if we do not know the table name it's hard to help.
Take care and glad to see your trying to learn Vba

 

[Peter_SSs]

Hi Peter,

Yup, i need for dynamically change between amount number of row/column table header and sidebar legend

Without further details of how/where to find the table, or whether it is a 'formal' table (ListObject) or just normal rows/columns, I can't offer anything further. If you have been able to get something working, that's great. Otherwise more details would need to be provided.