Solved: Reference Visible Cells within a Range

[JimS]

Below is the start (alright a bad start) to a routine that I need to use that will (hopefully) reference only the visible cells in a Dynamic Name Range.

I’m having trouble getting the tempRange line to work.

My range names are "Row_8", "Row_9", Row_10, down through Row_88.

Using this formula: =OFFSET('Data'!$G$8,0,0,1,COUNTA('Data'!$8:$8)-6)

Can this be done?

Thanks for any and all help…

JimS

[vba]
Sub ADD()

Dim x
Dim tempRange As Range

With Worksheets(“Data").Select

For x = 8 To 88

tempRange = Range("Row_" & x).SpecialCells(xlCellTypeVisible)

' Add it up (I got that covered)

Next x

End With

End Sub
[/vba]

[Paul_Hossler]

I think you're missing some Set's and that you want to use Union to 'join' the ranges. If by "Add them up" means you just want to sum the contents of each range, them the Union stuff is not needed, and you can delete a lot.

In either case, something to consider is what happens if Range ("Row_xx") have no visible cells, i.e. it's hidden or filtered out.

A quick attempt to add the Set's and Union, as well as catch Row_xx's that have no visible cells

[vba]
Option Explicit
Sub ADD()
Dim iRow As Long, iFirst As Long
Dim tempRange As Range, holdRange As Range, firstRange As Range


With Worksheets("Data")

.Select

Set holdRange = Nothing
Set tempRange = Nothing
Set firstRange = Nothing

iFirst = 8

'find first row that has visible cells (row_8, etc. 'might' be hidden
Do While firstRange Is Nothing And iRow < 88

On Error Resume Next
Set firstRange = Range("Row_" & iRow).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

iFirst = iFirst + 1
Loop

'if row_8 .. row_88 all have no visible cells, exit
If firstRange Is Nothing Then Exit Sub


'init accumulator with first range that has visbile cells
Set tempRange = firstRange

'check the rest, and IF row_xx has visile cells, then Union it
For iRow = iFirst To 88

Set holdRange = Nothing
On Error Resume Next
Set holdRange = Range("Row_" & iRow).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not holdRange Is Nothing Then
Set tempRange = Union(tempRange, holdRange)
End If

Next iRow

End With

End Sub
[/vba]

Not tested since I didn't have any sample data handy.

As an aside, if Range(Row_08) ... Range (Row_88) are adjacent, why not use (for ex.) Range("G8:Z88").SpecialCells .... ?

Paul

[JimS]

I need to sum by the individual row (using the visible cells only), one row at a time.

[Paul_Hossler]

[VBA]
Set tempRange = Range("Row_" & x).SpecialCells(xlCellTypeVisible)
[/VBA]

Paul

[JimS]

Thanks Paul...