• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hide rows based on specific value in specific column

YasserKhalil

YasserKhalil

Well-Known Member
Hello everyone
I have this code that hide rows in column G if cell has zero value or cell was empty
Code: 
Private Sub CommandButton1_Click()
    Dim Rng As Range
   
    CommandButton1.Caption = IIf(CommandButton1.Caption = "Hide", "Show", "Hide")
   
    Application.ScreenUpdating = False
        On Error Resume Next
        With ActiveSheet
            .AutoFilterMode = False
            .Range("A3:I3").AutoFilter Field:=7, Criteria1:="=0", Operator:=xlOr, Criteria2:=""
            Set Rng = .Range("G4:G" & .Cells(Rows.Count, "G").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
            .AutoFilterMode = False
           
            If CommandButton1.Caption = "Hide" Then
                Rng.EntireRow.Hidden = False
            Else
                Rng.EntireRow.Hidden = True
            End If
        End With
    Application.ScreenUpdating = True
End Sub

It is working well .. but I am searching for compact code .. or say another sub with parameters that I can call from any other sub
Thanks advanced for help
 
Thanks Mr. MarcL for reply
I didn't get Evaluate well ..
Can you explain me this line please
Code: 
VA = Filter(.Evaluate("TRANSPOSE(IF(" & AD & ">0,ROW(" & AD & ")))"), False, False)
 

Evaluate method computes a worksheet formula,
Filter function extracts data from an one dimension array :
for both see VBA inner help …​
 
Thanks Mr. MarcL
You repeated replies for me (VBA inner help) and I can't get to this help at all
As for filter method what false and false ?
After I use Evaluate (for knowing rows that I would deal with) how can I make use of it to hide and unhide rows .. in this case with arrays
 
As English is not my native language …

So it's just easy logic and just by reading VBA help of Filter function,
compare arguments in order with the help !

► Filter(Evaluate(), False, False)
= Filter(argu#1, argu#2, argu#3) in help :
→ Filter(sourcearray, match[, include[, compare]]) …

So yes, explanation was already in inner help before you wrote your post !
And if you have not this inner help (oh such a bad Excel install ‼),
information is also in MSDN website …

Evaluate produces an array,
Filter removes False values within this array …

Once you get a right array with only cells within rows to hide,
(you must mod formula using ADDRESS, it's just Excel basics formula !)
use Join function with this array to create a comma delimiter string
to use within Range.EntireRow.Hidden …

If you do not know Excel basics (formulas) and as you already have
a working code, the very matter is to keep a code you understand
just in case of a future mod !
The most important is not the better or shorter code
(and your actual code is not huge !)
but the code you are able to update in future !

___________________________________________________________
As Copy / Paste is not coding …
​
 
Thanks a lot for this great explanation ..
It seems that I will suffice my existing code that I hope to develop to be flexible .. so as to use in many other subroutines which is similar to that case
Thanks a lot for help
 
As explained in Hui's banner before to log in and
just above the post textbox within the tip frame,
without a sample file … as well with a crystal clear
explanation of the purpose, it will be difficult to propose anything !
As your code seems weird, why using a filter to test only one cell ?!
And I doubt if code is well working 'cause of how the button caption
is modified … (I will not be anymore connected to Web until tomorrow …)
​
 
Code: 
Private Sub CommandButton1_Click()
    CommandButton1.Caption = IIf(CommandButton1.Caption = "Hide", "Show", "Hide")
    Application.ScreenUpdating = False
    With Range("g3", Range("g" & Rows.Count).End(xlUp))
        Me.AutoFilterMode = False
        If CommandButton1.Caption = "Hide" Then
            .AutoFilter 1, "<>0", , , 0
        End If
    End With
    Application.ScreenUpdating = True
End Sub
If the range is getting bigger, the code for catching the row string by Join/Filter/Evaluate should get longer for error trap.
255 character restriction of sub-string for Range/Row.
 
Thank you very much Mr. Jindon
How could I make your code deal with blanks too as it deals with zeros only
Another question : what do you mean by 255 character restriction?
Can you post the sample for using Join/filter/evaluate ..
 
1) Change to
Code: 
            .AutoFilter 1, "<>0", 1, "<>", 0
2) Range(sub_tring) limited to 255 characters, so as the range to be hidden glows bigger, sub_string will get greater like more than 255.
Code: 
   Dim txt as String
   txt=Join(VA,",")
    Do While Len(txt) > 250
        x = InStrRev(txt, ",", 255)
        Range(Left$(txt, x - 1)).EntireRow.Hidden = True
        txt = Mid$(txt, x + 1)
    Loop
    If Len(txt) Then Range(txt).EntireRow.Hidden = False
Will get slower and slower, so useless in this case.
 
Thank you very very much for help
Can you explain the parameters in this line
Code: 
 .AutoFilter 1, "<>0", 1, "<>", 0
And can I add more conditions for this line of filter ..
 
Ok thanks I searched in MSDN and found it .. I am used to abbreviation method that is case

As for second suggestion I got an error at this line
Code: 
Txt = Join(VA, ",")
 
You got VA in you post #4, so I just used it.
If VA is not an array then it should raise the error.
 
Ok I fixed that part
Now I got an error at this line
Code: 
Range(Txt).EntireRow.Hidden = False

This is my last try
Code: 
Private Sub CommandButton1_Click()
    Dim VA, X, AD$
    Dim Txt As String

    CommandButton1.Caption = IIf(CommandButton1.Caption = "Hide", "Show", "Hide")
   
    Application.ScreenUpdating = False
        AD = Sheets("Sheet1").Range("G3", Range("G" & Rows.Count).End(xlUp)).Address
   
        VA = Filter(Sheet1.Evaluate("TRANSPOSE(IF(" & AD & ">0,ROW(" & AD & ")))"), False, False)
        Txt = Join(VA, ",")
        Do While Len(Txt) > 250
            X = InStrRev(Txt, ",", 255)
            Range(Left$(Txt, X - 1)).EntireRow.Hidden = True
            Txt = Mid$(Txt, X + 1)
        Loop
        If Len(Txt) Then Range(Txt).EntireRow.Hidden = False
    Application.ScreenUpdating = True
End Sub
 
Of course...
Code: 
VA = Filter(Sheet1.Evaluate("TRANSPOSE(IF(" & AD & ">0,ROW(" & AD & ")))"), False, False)
It is not correct.

Any further question, wait for Marc L.
I'll be off line shortly.
 
jindon well points out the characters limit in Range,
so since its code in post #12 corrects your initial code and
works whatever the number of lines to hide, this is your compacted code !

In case of few lines to hide, you could go further with Evaluate way
but could be not more compacted as jindon's code …
​
 
Thank you very much for great help
I just needed to learn new methods ..Can you point me the error in the code in post #19 and if possible correct it for me?
 
Just for "compact"
Code: 
Private Sub CommandButton1_Click()
    Dim i As Long
    CommandButton1.Caption = IIf(CommandButton1.Caption = "Hide", "Show", "Hide")
    Application.ScreenUpdating = False
    For i = 4 To Range("g" & Rows.Count).End(xlUp).Row
         Rows(i).Hidden = ((Cells(i, "g") = 0) + (Cells(i, "g") = "")) * (Me.CommandButton1.Caption = "Show")
    Next
    Application.ScreenUpdating = True
End Sub
 
You are a school Mr. Jindon
Now I can post no more posts...Solved
Thank you very much for this great and wonderful help
 
You must log in or register to reply here.
Back
Top