SpecialCells

[Guest]

I am trying to access the next cell in a Range of SpecialCells of xlVisible
type only. For some reason when I use
Dim r as Range, rng1 as range
set rng1 = colums(3).SpecialCells(xlVisible)
For each r in rng1
blah,blah
Next
It goes through the visible cells. BUT when I replace the For Each loop with

rng1.Cells(2,1).Value
rng1.Cells(3,1).Value

where cells(3,1,) in the normal worksheet is hidden, it will return the
hidden cell(the hidden cell right after the non-hidden cell cells(2,1)
instead of returning the next visible cell in the rng1 object.
Any ideas? I want to compare the contents of two visible cells to see if
they are duplicates, and delete the second cell and the hidden cells after
the duplicate.

 

[Guest]

If you read Chips example at

http://www.cpearson.com/excel/cells.htm

he notes that
Range("A1:B2")(5) refers to Cell A3, Range("A1:B2")(14) refers to Cell B7,
etc.

Notice that there are not 5 cells in A1:B2, yet you can still reference cell
A3.

This is a guess, but your range is comprised of several areas. Since you
don't explicitly state the area, the macro assumes the first area. When the
counter variable exceeds the number of cells in the first area, it runs over
to the hidden cells. Maybe you can loop through the areas then the cells??

for i = 1 to rng1.areas.count
for t = 1 to rng1.areas(i).cells.count
rng1.areas(i).cells(t)

 

[Guest]

thanks, this got me on the right track. I guess I still don't understand
areas very well. But I understand that with the

set rng1 = .columns("A").SpecialCells(xlVisible)

the rng1 has areas that are consecutive visible cells, each area has one
cell. I am able to reference the next visible cell with

For k = 1 to rng1.areas.count
rng1.Areas(k).Cells(1) 'current visible cell
rng1.Areas(k+1).Cells(1) 'next visible cell
Next

And that solves my problem! Thanks a ton

 

[Guest]

So I have another problem someone might be able to help me with.....
with the same program as before, for some reason when rng1 is the Range of
visible cells and there are two non-grouped cells at the bottom of the Range
( the rest of the cells are grouped and minimized) the rng1.Areas.Count
returns the number of all visible cells EXCEPT the last non-grouped cell at
the bottom of the Range is excluded. So when i access
rng1.Areas(rng1.Areas.Count).Cells(1), it returns the second to last visible
cell. When I try seeing if the last area has more than one cell, the
rng1.Areas(rng1.Areas.Count).Cells.Count returns a huge number.
Any ideas?

 

[Guest]

Is there is a hidden row between your second to last visible cell and the
last visible cell? If not, the last cell is included in the same area as the
next to last cell.

The cell count for the last area will include all of the empty cells below
your data in the spreadsheet (since they are visible).

Instead of using the entire column B, you could narrow it down to only look
at B1 through the last visible cell with data in it (blank cells in the
middle of your data would be included in rng1) with code similar to below.

Sub test2()
Dim rng1 As Range
Dim i As Long
Dim t As Long

With Sheet1
Set rng1 = Intersect(.Range("B1", _
.Cells(.Rows.Count, 2).End(xlUp)), _
.Columns(2).SpecialCells(xlCellTypeVisible))
End With

If Not rng1 Is Nothing Then
For i = 1 To rng1.Areas.Count
MsgBox rng1.Areas(i).Cells.Count
For t = 1 To rng1.Areas(i).Cells.Count
MsgBox rng1.Areas(i).Cells(t).Value
Next t
Next i
End If

End Sub


I don't know of any built in method of getting the next cell using a
For/Each/Next loop, but this seemed to work for me.

Sub test3()
Dim rng1 As Range
Dim i As Long
Dim t As Long
Dim rngCell As Range
Dim rngNext As Range

With Sheet1
Set rng1 = Intersect(.Range("B1", _
.Cells(.Rows.Count, 2).End(xlUp)), _
.Columns(2).SpecialCells(xlCellTypeVisible))
End With

If Not rng1 Is Nothing Then
For Each rngCell In rng1
MsgBox rngCell.Address
Set rngNext = NextCell(rng1, rngCell)
If Not rngNext Is Nothing Then _
MsgBox rngNext.Address
Next rngCell
End If
End Sub

Function NextCell(ByRef rngData As Range, ByRef rngCurrentCell As Range) As
Range
Dim rngCell As Range
Dim ExitLoop As Boolean

For Each rngCell In rngData
If ExitLoop Then
Set NextCell = rngCell
Exit For
End If
If rngCell.Address = rngCurrentCell.Address Then _
ExitLoop = True
Next rngCell
End Function