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

[mxoax32]

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

 

[JLGWhiz]

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

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]

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]

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]

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.

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]

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]

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]

There was a typo in the code for post #4, here is the corrected code, with comments.

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.