Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells
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) "mike" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells
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 "JMB" wrote: 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) "mike" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells
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? "mike" wrote: 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 "JMB" wrote: 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) "mike" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells
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 "mike" wrote: 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? "mike" wrote: 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 "JMB" wrote: 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) "mike" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SpecialCells | Excel Programming | |||
SpecialCells & AddressLocal | Excel Programming | |||
Specialcells | Charts and Charting in Excel | |||
SpecialCells(xlCellTypeLastCell) | Excel Programming | |||
SpecialCells(xlCellTypeFormulas) | Excel Programming |