Hide a row when detect any value in a row

francozzy

New Member
Joined
Apr 3, 2018
Messages
26
Hi ,

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

here below sample table :

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Items / Stocks[/TD]
[TD="align: center"]Nov[/TD]
[TD="align: center"]Dec[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[/TR]
[TR]
[TD="align: center"]Office Desk[/TD]
[TD="align: center"]3 pcs[/TD]
[TD="align: center"]6 pcs[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10 pcs[/TD]
[/TR]
[TR]
[TD="align: center"]Office Chair[/TD]
[TD="align: center"]20 pcs[/TD]
[TD="align: center"]40 pcs[/TD]
[TD="align: center"]10 pcs[/TD]
[TD="align: center"]80 pcs[/TD]
[/TR]
[TR]
[TD="align: center"]Printer[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]fax machine[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3 pcs[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]filling cabinet[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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


Code:
        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 ?
 
Upvote 0
Give this a try in a copy of your workbook.
Code:
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Assuming your image here is showing column A to E
If that is not true then give me more specifics:

Try this:
Code:
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
 
Upvote 0
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:
Code:
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
Code:
[I]    For[B] i = 2[/B] To Lastrow [/I]

second parameter is parameter that represent by 2
Code:
[I] Application.WorksheetFunction.CountA([B]Cells(i, 2)[/B].Resize(, LastColumn - 1)) [/I]

It's Great
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top