Hide & unhide rows within range when double click cell

[abdo meghari]

Hello
I search for way to hide & unhide rows within range at once based on double click cell in A20 if range B;F is empty
so if I double click cell in A20 then will hide rows after row 20 except TOTAL row and empty range(B:F)
example:

فاتورة.xlsm
	A	B	C	D	E	F	G
20	ITEM	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
21	1	BS 1200R20	G580	JAP	11.00	12.00	132.00
22	2						0.00
23							0.00
24							0.00
25							0.00
26							0.00
27							0.00
28							0.00
29							0.00
30							0.00
31							0.00
32							0.00
33							0.00
34							0.00
35							0.00
36							0.00
37							0.00
38							0.00
39							0.00
40							0.00
41							0.00
42							0.00
43							0.00
44							0.00
45							0.00
46							0.00
47	TOTAL						132.00
فاتورة

Cell Formulas
Range	Formula
G21:G46	G21	=E21*F21
G47	G47	=SUM(G21:G46)

Cells with Data Validation
Cell	Allow	Criteria
B26	List	BS 1200R20;BS 700R16;BS 315/80R22.5;BS 315/80RR.5;BS 225/55R16;BS 205/70R15C;BS 215/70R15C;FR 215/75R15;BS 255/70R15C ;GO 315/80R22.5;TQ 195/65R15;KB 215/70R15C;WS 285/70R19.5;GO 1200R20;FU 265/75R16;VAR 70A;AUTO 68A;XPRO 70A;XPRO 60A;Q8 10W-40;LIQ 10W-40
B21:B22	List	BS 1200R20;BS 700R16;BS 315/80R22.5;BS 315/80RR.5;BS 225/55R16;BS 205/70R15C;BS 215/70R15C;FR 215/75R15;BS 255/70R15C ;GO 315/80R22.5;TQ 195/65R15;KB 215/70R15C;WS 285/70R19.5;GO 1200R20;FU 265/75R16;VAR 70A;AUTO 68A;XPRO 70A;XPRO 60A;Q8 10W-40;LIQ 10W-40
C21	List	G580;R187
D21	List	JAP

when double click in cell A20 then should be

in.xlsm
	A	B	C	D	E	F	G
20	ITEM	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
21	1	BS 1200R20	G580	JAP	11.00	12.00	132.00
47	TOTAL						132.00
v

Cell Formulas
Range	Formula
G21	G21	=E21*F21
G47	G47	=SUM(G21:G46)

Cells with Data Validation
Cell	Allow	Criteria
B21:B22	List	BS 1200R20;BS 700R16;BS 315/80R22.5;BS 315/80RR.5;BS 225/55R16;BS 205/70R15C;BS 215/70R15C;FR 215/75R15;BS 255/70R15C ;GO 315/80R22.5;TQ 195/65R15;KB 215/70R15C;WS 285/70R19.5;GO 1200R20;FU 265/75R16;VAR 70A;AUTO 68A;XPRO 70A;XPRO 60A;Q8 10W-40;LIQ 10W-40
C21	List	G580;R187
D21	List	JAP

and if I want to return showing rows when double click in A20 again
then should be

in.xlsm
	A	B	C	D	E	F	G
20	ITEM	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
21	1	BS 1200R20	G580	JAP	11.00	12.00	132.00
22	2						0.00
23							0.00
24							0.00
25							0.00
26							0.00
27							0.00
28							0.00
29							0.00
30							0.00
31							0.00
32							0.00
33							0.00
34							0.00
35							0.00
36							0.00
37							0.00
38							0.00
39							0.00
40							0.00
41							0.00
42							0.00
43							0.00
44							0.00
45							0.00
46							0.00
47	TOTAL						132.00
v

Cell Formulas
Range	Formula
G21:G46	G21	=E21*F21
G47	G47	=SUM(G21:G46)

Cells with Data Validation
Cell	Allow	Criteria
B26	List	BS 1200R20;BS 700R16;BS 315/80R22.5;BS 315/80RR.5;BS 225/55R16;BS 205/70R15C;BS 215/70R15C;FR 215/75R15;BS 255/70R15C ;GO 315/80R22.5;TQ 195/65R15;KB 215/70R15C;WS 285/70R19.5;GO 1200R20;FU 265/75R16;VAR 70A;AUTO 68A;XPRO 70A;XPRO 60A;Q8 10W-40;LIQ 10W-40
B21:B22	List	BS 1200R20;BS 700R16;BS 315/80R22.5;BS 315/80RR.5;BS 225/55R16;BS 205/70R15C;BS 215/70R15C;FR 215/75R15;BS 255/70R15C ;GO 315/80R22.5;TQ 195/65R15;KB 215/70R15C;WS 285/70R19.5;GO 1200R20;FU 265/75R16;VAR 70A;AUTO 68A;XPRO 70A;XPRO 60A;Q8 10W-40;LIQ 10W-40
C21	List	G580;R187
D21	List	JAP

thanks

 

[kevin9999]

Hi Abdo, try the following on a copy of your workbook:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A20")) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Cancel = True
        Dim c As Range
        For Each c In Range("B21:B" & Cells(Rows.Count, "A").End(xlUp).Row - 1)
            If c.EntireRow.Hidden = True Then
                c.EntireRow.Hidden = False
            Else
                If c = "" Then
                    c.EntireRow.Hidden = True
                End If
            End If
        Next c
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

 

[DanteAmor]

Hello Abdo:

Here is another code for you to consider:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Address(0, 0) <> "A20" Then Exit Sub
  Cancel = True
  Dim r As Range
  For Each r In Range("B21", Range("A:A").Find("TOTAL", , xlValues, xlWhole, xlByRows, xlPrevious, False).Offset(-1, 1))
    Rows(r.Row).Hidden = (Rows(r.Row).Hidden = False) * (r & r.Offset(, 1) & r.Offset(, 2) & r.Offset(, 3) & r.Offset(, 4) = "") 'range(B:F)
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[abdo meghari]

awesome solution !
by the way @kevin9999 's code is fast than @DanteAmor's code , slight slow.
thank you so much guys .

 

[kevin9999]

awesome solution !
by the way @kevin9999 's code is fast than @DanteAmor's code , slight slow.
thank you so much guys .

Glad we could help Abdo, and thanks for the feedback

 

[DanteAmor]

Try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Address(0, 0) <> "A20" Then Exit Sub
Application.ScreenUpdating = False
  Cancel = True
  Dim r As Range
  For Each r In Range("B21", Range("A:A").Find("TOTAL", , xlValues, xlWhole, xlByRows, xlPrevious, False).Offset(-1, 1))
    Rows(r.Row).Hidden = (Rows(r.Row).Hidden = False) * (r & r.Offset(, 1) & r.Offset(, 2) & r.Offset(, 3) & r.Offset(, 4) = "") 'range(B:F)
  Next
Application.ScreenUpdating = True
End Sub