VBA hide row based on value in two columns: one text and one numeric.

mxoax32

New Member
Joined
Jan 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I formulated a financial statement importing information from an external SQL based accounting software. The numbers spit out into excel and I have modified the sheet to look presentable. We occasionally have zero values, but want to hide those rows if they are zero. There are also titles that need to be hidden, and those columns after the titles have blank values. I have a VBA for the whole worksheet that hides rows with zero balances. I need a VBA that hides rows that contain text in one column AND zero or blank in another column.

I don't know or write macros. The macro I have is copied and pasted, and modified to fit my data set. The columns I need the macro to read are columns C and D rows 20:200.

Here is my current VBA:
Sub SelectionHide()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
xSh.Select
Call RunCode
Next
Application.ScreenUpdating = True
End Sub
Sub RunCode()
Application.ScreenUpdating = False
Application.Calculation = xlManual

Rows("20:300").Hidden = False

For Each c In Range("D20:D300")
If c.Value = 0 And c.Value <> "" And c.Offset(0, "1.8").Value = 0 And c.Offset(0, "1.8").Value <> "" Then Rows(c.Row).Hidden = True
Next c

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
Dim r As Range
Range("C20:C200").EntireRow.Hidden = False
For Each r In Range("C20:C200")
    If r <> "" And (r.Offset(, 1) = 0 Or r.Offset(, 1) = "") Then
        r.EntireRow.Hidden = True
    End If
Next

Would replace this


VBA Code:
Rows("20:300").Hidden = False
For Each c In Range("D20:D300")
If c.Value = 0 And c.Value <> "" And c.Offset(0, "1.8").Value = 0 And c.Offset(0, "1.8").Value <> "" Then Rows(c.Row).Hidden = True
Next c
 

mxoax32

New Member
Joined
Jan 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Close! So I have titles in column C that do not have any values in column D that need to remain.

The function I need is: if text in column C AND numeric value in column D is Zero, then Hide, else show. So if text in column C and blank in column D, then show.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
Dim r As Range
Range("C20:C200").EntireRow.Hidden = False
For Each r In Range("C20:C200")
If r <> "" And IsNumeric(r.Offset(, 1) = 0 Then
r.EntireRow.Hidden = True
End If
Next
 

mxoax32

New Member
Joined
Jan 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Close. The new VBA minimized everything. I reversed your true and false, then it only minimized the blank rows altogether. I have a small example of what I'm working on below.

1611587187601.png


What I need in this situation is to leave the blank row unhidden above "Construction Revenue", leave the row "Construction Revenue" unhidden, leave the row "Completed Contracts" unhidden, HIDE "Other Contracts", leave the row "Total Construction Revenue" unhidden, and leave the row after "Total Construction Revenue" unhidden.

So:
1) if row c and d are blank then hidden = false
2) if row c is not blank and row d <> 0 then hidden = false
3) if row c is not blank and row d = 0 then hidden = true
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You first need to use terms that the Excel conventions use. Rows are horizontal entries on a sheet. Columns are vertical entries on a sheet. If we can stay with the conventions we will all be referring to the same thing, otherwise we work in a state of confusion.
 

mxoax32

New Member
Joined
Jan 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I'm sorry, I meant to say:
1) if columns c and d are blank then row hidden = false
2) if column c is not blank and column d <> 0 then row hidden = false
3) if column c is not blank and column d = 0 then row hidden = true
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
There was a typo in the code for post #4, here is the corrected code, with comments.

VBA Code:
Dim r As Range
Range("C20:C200").EntireRow.Hidden = False 'Unhides all rows
    For Each r In Range("C20:C200")
        If r.Value <> "" And IsNumeric(r.Offset(, 1)) = 0 Then 'Hides rows Col="" and Col D=0
            r.EntireRow.Hidden = True
        End If
    Next

The code unhides all rows first so that any rows that may have changed since the previous run of the macro will be addressed in the current run. The only rows to be hidden are the ones with no value in column C while column D has a value of zero (0). If someone has typed an O instead of 0 in column D, then the row will not hide. the zero must be numeric. also the code does not recognize a short dash (-) as zero nor blank.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,809
Messages
5,627,019
Members
416,215
Latest member
Ostie3994

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
Top