View Full Version : Solved: Reference Visible Cells within a Range
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
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
Paul_Hossler
01-04-2012, 03:25 PM
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
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
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
I need to sum by the individual row (using the visible cells only), one row at a time.
Paul_Hossler
01-04-2012, 07:52 PM
Set tempRange = Range("Row_" & x).SpecialCells(xlCellTypeVisible)
Paul
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.