hide row if 2 cell of same row is blank

annadinesh

Board Regular
Joined
Mar 1, 2017
Messages
123
Office Version
  1. 2019
Platform
  1. Windows
Dear Experts

I want a vba code for

if any cell of (B23:B30) of (E23:E30) is blank the hide that row

and if a cell contains value in (B23:B30) and another cell blank (E23:E30) of same row then it should not hode the row

the hide option should work if both cell of a row is blank


Service Calculator test.xls
BCDEF
2ESTIMATE
324.06.2024
4Parts DescriptionLabour Description
5NameAmountNameAmount (incl. Tax)
221 Additional Parts & Labour
231 
241 
251 
261 
271 
281 
29 
30
31* The Estimated amount may vary with the final invoice, subject to the job carried out and the cost of parts
32TOTAL PARTS TOTAL LABOUR (incl Tax) 
33GRAND TOTAL (Approx.) 
Quotation
Cell Formulas
RangeFormula
F3F3=TODAY()
D22:D29D22=IFERROR(VLOOKUP(B22,PMS!A$3:AE$29,MATCH(D$3,PMS!A$3:AE$3,0),0),"")
D32D32=SUM(D6:D29)
F32F32=SUM(F6:F30)
E33E33=D32+F32
Named Ranges
NameRefers ToCells
MODELS=PMS!$A$3:$AD$3D22:D29
Parts=PMS!$A$3:$A$29D22:D29
Cells with Data Validation
CellAllowCriteria
D3:E3List=MODELS
B6:B29List=Parts
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try running this code:
VBA Code:
Sub MyHideRows()

    Dim r As Long
    
'   Loop through rows 23-30
    For r = 23 To 30
'       See if BOTH columns B and E are blank, and if so, hide row
        If Cells(r, "B") = "" And Cells(r, "E") = "" Then
            Rows(r).EntireRow.Hidden = True
        Else
            Rows(r).EntireRow.Hidden = False
        End If
    Next r
    
End Sub
 
Upvote 0
Solution
Try running this code:
VBA Code:
Sub MyHideRows()

    Dim r As Long
   
'   Loop through rows 23-30
    For r = 23 To 30
'       See if BOTH columns B and E are blank, and if so, hide row
        If Cells(r, "B") = "" And Cells(r, "E") = "" Then
            Rows(r).EntireRow.Hidden = True
        Else
            Rows(r).EntireRow.Hidden = False
        End If
    Next r
   
End Sub
THANKS
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,173
Members
452,615
Latest member
bogeys2birdies

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