• 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

Really I don't know what do you mean by (mod formulas using ADDRESS)
May you fix it for me .. please do me a favor?
 

You already had a sample within post #3 first link !
So show me your formula try …

And without ADDRESS you can use result within a loop !
​
 
I already used this formula from Post#3 .. but I don't know if it is suitable or not .. used it and got the error
Never mind .. I can depend on any of the perfect solutions provided and presented by Mr. Jindon
Thanks a lot for help
 
I gave this formula exercise yesterday to a very beginner Excel formulas
teenager and he answered the good basic formula in a couple of minutes :

TRANSPOSE(IF(G3:G55=0,ADDRESS(ROW(G3:G55),7,4))) …

(he's not a genius, just a normal 13 years old schoolboy !)

So just needs 7 codelines using Evaluate method …
​
 
Mr. MarcL to your knowledge .. we didn't study any kind of programming here in our country .. it is just a hobby and curiosity and I am seeking to learn .. and everyday I learn new things but still ignorant .. there is no relation between age and knowledge ..
And may be because of English is not my native language I didn't get most of the speech ..!

After your last post I tried but nothing happened
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
  'TRANSPOSE(IF(G3:G55=0,ADDRESS(ROW(G3:G55),7,4)))
        VA = Filter(Sheet1.Evaluate("TRANSPOSE(IF(" & AD & "=0,ADDRESS(ROW(" & AD & "),7,4)))"), 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
 
Thanks a lot Mr. Jindon
I have replaced False with True and it works fine for hiding only but if I clicked the command button again it didn't show the rows ..
Have I missed anything else?
 
That's wonderful..
At last solved completely ..
You are amazing Mr. Jindon
Thank you very much for both of you .. You are BRILLIANT Experts
Best Regards
 
All I learned in VBA was just using Macro recorder
and reading its inner help !
Before VBA I learned some programming language like
BASIC (MS,GW,QB,Turbo, …), Pascal, … just by reading manuals
and P R A C T I C I N G …
At this time there was no existing forum to do my job at my place !

Yes it's not an age question but just from L O G I C !
If you traduce your code in your spoken language,
you must see this problem because you don't ever test if rows
must be hidden or not, you just always show them :
Range(Txt).EntireRow.Hidden = False ‼​
As it's very easy to follow your own code in step by step mode
and check out yourself where is the problem …

So before to write the first codeline, you must think of logic on paper.
No initial thinking before coding equals wasting time !
Like here the formula is right but not the logic : rows are always visible !

As well before to see if a code could be compacted, the code must
works and your initial code can not as I gentle warned you,
the reason why I gave you another way just to think about your logic …
You wrote about your initial code « It is working well. » but in fact not ‼

Best Excel VBA programmers are those who knows Excel basics
like formulas, object model, …


So the revisited jindon's code …​
Code: 
Private Sub CommandButton1_Click()
    Dim R As Long
    CommandButton1.Caption = IIf(CommandButton1.Caption = "Hide", "Show", "Hide")
    Application.ScreenUpdating = False
For R = 4 To Cells(Rows.Count, 7).End(xlUp).Row
    Rows(R).Hidden = (Cells(R, 7).Value = 0) * (CommandButton1.Caption = "Show")
Next
    Application.ScreenUpdating = True
End Sub
… could be not accepted in a professional context
because if there is nothing to hide, after clicking on Hide
button caption is set to Show but there is no hidden row …
Yes, it's not a big concern and with burning a couple of neurons
and an easy logic it's easy to mod …

Now as there is few lines to hide in your sample workbook,
the direct combination of an filtered array formula and Join function works :​
Code: 
    Dim VA
    If CommandButton1.Caption = "Show" Then
        Me.UsedRange.Rows.Hidden = False:  CommandButton1.Caption = "Hide"
    Else
        VA = Filter(Evaluate(Replace("TRANSPOSE(IF(#=0,ADDRESS(ROW(#),7,4)))", "#", [A3].CurrentRegion.Columns(7).Address)), False, False)
        If UBound(VA) > -1 Then Range(Join(VA, ",")).EntireRow.Hidden = True: CommandButton1.Caption = "Show"
    End If

But with more lines to hide with a text bigger than 255 characters,
the previous code generates an error.

So again just with easy logic, it's not difficult to loop a rows array :​
Code: 
        Dim B%, V
    If CommandButton1.Caption = "Show" Then
        Me.UsedRange.Rows.Hidden = False:  CommandButton1.Caption = "Hide"
    Else
            Application.ScreenUpdating = False
        For Each V In Filter(Evaluate(Replace("TRANSPOSE(IF(#=0,ROW(#)))", "#", [A3].CurrentRegion.Columns(7).Address)), False, False)
            Rows(V).Hidden = True:  B = 1
        Next
            If B Then CommandButton1.Caption = "Show"
            Application.ScreenUpdating = True
    End If
Do you like it ? So thanks to click on bottom right Like !

​
 
That's really wonderful .. I like a lot your way of thinking .. it is really great and I think I have to think carefully and in practical way to handle any problem
Thanks a lot for the time spent in typing the last post
Best and kind regards
 
Thanks !

First thing to think before to code :
what can be done directly within Excel ?
Here it's just using a filter, the way to go as your initial try, no VBA needed …

If a code is really needed, as almost all manual operations could be done
in VBA, using Macro recorder and a bit of knowledge (experience),
this is your compact code :​
Code: 
Private Sub CommandButton1_Click()
 If Me.FilterMode Then Cells(1).AutoFilter
 If CommandButton1.Caption = "Hide" Then Range("G3", Cells(Rows.Count, 7).End(xlUp)(0)).AutoFilter 1, ">0", , , 0
    CommandButton1.Caption = IIf(Me.FilterMode, "Show", "Hide")
End Sub
Enjoy !
​
 
You must log in or register to reply here.
Back
Top