OaklandJim
Well-known Member
- Joined
- Nov 29, 2018
- Messages
- 863
- Office Version
- 365
- Platform
- Windows
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
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.
Beyond basic! But I cannot get
VBA Code:
rCell.EntireRow.Hidden = True
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 |
VBA Code:
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