Use VBA to hide rows based on the cell value

[andrewb90]

Hello All,

I am a little stumped on this, and unfortunately the macro recorder is no good here...

What I am trying to do is look at the active sheet and if there is no data in D5, then that row would be hidden. I would want to continue all the way down to row 70, however, Rows 15, 25, & 38 would need need to be excluded.

Any assistance would be greatly appreciated.

Thanks,

Andrew

 

[WarPigl3t]

Sub myMacro()
Dim myArrayExclusions(15, 25, 38) as integer
firstRow = 2
lastRow = Range("D" & Rows.Count).End(xlUp).Row
r = firstRow
Do Until r > lastRow
myValue = Range("D" & r).Value
If myValue = "" Then
excludeBoolean = False
For Each a in myArrayExclusions
If a = r Then
excludeBoolean = True
End If
Next a
If excludeBoolean = False Then
Rows(r).Hidden = True
Else
Rows(r).Hidden = False
End If
End If
r = r + 1
Loop
End Sub

 

[JoeMo]

Hello All,

I am a little stumped on this, and unfortunately the macro recorder is no good here...

What I am trying to do is look at the active sheet and if there is no data in D5, then that row would be hidden. I would want to continue all the way down to row 70, however, Rows 15, 25, & 38 would need need to be excluded.

Any assistance would be greatly appreciated.

Thanks,

Andrew

Does this do what you want?

Sub Andrew()
Dim c As Range
Application.ScreenUpdating = False
ActiveSheet.Rows("5:70").Hidden = False
For Each c In ActiveSheet.Range("D5:D70")
       If c.Row <> 15 And c.Row <> 25 And c.Row <> 38 Then
              If IsEmpty(c) Then c.EntireRow.Hidden = True
       End If
Next c
Application.ScreenUpdating = True
End Sub

 

[andrewb90]

Hi Joe,

I just tried yours, and it works great! Thank you for also posting, War!

 

[JoeMo]

Hi Joe,

I just tried yours, and it works great! Thank you for also posting, War!

You are welcome - thanks for the reply'

 

[Michael M]

Maybe

Sub MM1()
Dim r As Long
Rows("5:70").Hidden = False
    For r = 5 To 70
        If r <> 15 And r <> 25 And r <> 38 Then
            If Range("D" & r).Value = "" Then Rows(r).Hidden = True
        End If
    Next r
End Sub

 

[Michael M]

Aaah...darn...too quick for me Joe ...ray:

 

[Rick Rothstein]

What I am trying to do is look at the active sheet and if there is no data in D5, then that row would be hidden. I would want to continue all the way down to row 70, however, Rows 15, 25, & 38 would need need to be excluded.

I may have a simple solution for you, but it depends on what is in your cells. What is in cells D5:D14, D16:D24, D26:D37, D39:D70... constants or formulas? Same question for D15, D25 and D38... are there constants or formulas in them?

 

[andrewb90]

The cells should all have text in them (names, specifically) the skipped cells are merged cells that always contain text but always need to visible.

Edit: They are all formulas, actually. They will all return values as text, as that is what will be in the cells that the formulas reference.

 

[Rick Rothstein]

Edit: They are all formulas, actually. They will all return values as text, as that is what will be in the cells that the formulas reference.

Since they are all formulas, there is no simple macro available; however, if you will never have more than 40 non-contiguous blank areas in D5:D70, this non-looping macro can be used...

[table="width: 500"]
[tr]
	[td]Sub HideRowsWhereD5D70IsBlank()
  Rows("5:70").Hidden = False
  Range(Replace(Application.Trim(Join(Evaluate("TRANSPOSE(IF(D5:D70="""",ADDRESS(ROW(D5:D70),4,4,1)&"":""&ADDRESS(ROW(D5:D70),4,4,1),""""))"), " ")), " ", ",")).EntireRow.Hidden = True
  Range("15:15,25:25,38:38").EntireRow.Hidden = False
End Sub[/td]
[/tr]
[/table]