Cannot hide a row for a specific cell in VBA. I suppose that I need a second set of eyes?

[OaklandJim]

Looping cells in a one column range containing seven cells. If a cell's content Len is 0 or 1 then hide its row. That way empty rows are not shown (are hidden) in the range of seven cells.

Beyond basic! But I cannot get

rCell.EntireRow.Hidden = True

to work where rCell is a single celled range object used to iterate through the seven cells. What might I try?

Here are the seven cells to look into. They are the seven cells below the header.

SadeInsurancce.xlsm
	D
6	Provider and Plan Type
7	Aetna Plan G, PPO
8	Blue Shield Plan G, HMO
9	Humana Plan G, PPO
10
11
12	Healthnet Innovative, PPO
13	Other Plan G, PPO
Result

Cell Formulas
Range	Formula
D7:D9,D12:D13	D7	=INDEX(Providers!ProviderNames,rrIndex)&CHAR(10) & INDEX(Providers!PlanNames,rrIndex) & IF(INDEX(Providers!Plan_Types,rrIndex)="", "", IF(INDEX(Providers!PlanNames,rrIndex) = "", "", ", ") & INDEX(Providers!Plan_Types,rrIndex))
D10	D10	=INDEX(Providers!ProviderNames,rrIndex) & CHAR(10) & INDEX(Providers!PlanNames,rrIndex) & IF(INDEX(Providers!Plan_Types,rrIndex)="", "", IF(INDEX(Providers!PlanNames,rrIndex) = "", "", ", ") & INDEX(Providers!Plan_Types,rrIndex))

Named Ranges
Name	Refers To	Cells
Providers!Plan_Types	=Providers!$E$5:$E$10	D12:D13, D7:D10
Providers!PlanNames	=Providers!$D$5:$D$10	D12:D13, D7:D10
Providers!PlanTypes	=Providers!$E$5:$E$10	D12:D13, D7:D10
Providers!ProviderNames	=Providers!$C$5:$C$10	D12:D13, D7:D10
rrIndex	=Result!$B7	D7

Sub CopyTable()

    Dim rPlanNamesCells As Range
   
    Dim rCell As Range
   
    Dim iCell As Long
   
'   Set variable rPlanNamesCells to "point" to cells in results table
'   containing plan names in Result sheet. Note that this includes the
'   cell containing the label for options with "special" characteristics.
'   Note hardwired count of rows in the table (7).
    Set rPlanNamesCells = [Result].Range("Header_ProviderName").Cells(2).Resize(7)
   
'Shows correct range.
Debug.Print "rPlanNamesCells = " & rPlanNamesCells.Address

'For indexing cells for debugging.
iCell = 0

    For Each rCell In rPlanNamesCells
 
Debug.Print Chr(10)

'Increment cell index # for debugging.
iCell = iCell + 1
   
'       Hide empty rows. A cell may contain no characters or one character
'       (a line feed character).
        If Trim(rCell.Value) = "" Or Len(rCell) = 1 _
         Then
            Debug.Print "cell " & iCell & ". " & "rCell.value is nothing"
           
'           Hide the empty cells' row.
            rCell.EntireRow.Hidden = True
       
        Else
       
            Debug.Print "cell " & iCell & ". " & "cell value = " & rCell.Value
       
        End If
   
    Next rCell
   
'   (re)Expose all rows in the results table.
    rTable.EntireRow.Hidden = False
   
End Sub

 

[Sam_D_Ben]

May be something like this,

Sub CopyTable()
    Dim ws As Worksheet
    Dim rPlanNamesCells As Range
    Dim rCell As Range

    Set ws = Worksheets("Result")
    Set rPlanNamesCells = ws.Range("Header_ProviderName").Cells(2).Resize(7)

    For Each rCell In rPlanNamesCells
        If Trim(rCell.Value) = "" Or Len(rCell.Value) = 1 Then
            rCell.EntireRow.Hidden = True
        End If
    Next rCell
End Sub

 

[OaklandJim]

Thanks for the response but that facet of my code is correct so no need to change it.

Here is what I did. I was hiding rows then reexposing them in the same sub. So, row hiding was working as coded originally. It's just that I reexposed the rows so could not see rows that are hidden.

This is a true brain fart.

Issue solved.