Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select visible cells using vba
Hi there,
Using vba code, I want to select a cell that has been filtered? ie. Range("A1").select Activecell.offset(2,0).select - this selects Range("A3") but is not visible. I want it to consider visible cells only. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select visible cells using vba
Range("A1").Select do ActiveCell.offset(1,0).Select Loop While ActiveCell.EntireRow.Hidden = True Did you want to select the second visible cell below the current selection rather than the next visible cell? -- Regards, Tom Ogilvy "Tony" wrote in message ... Hi there, Using vba code, I want to select a cell that has been filtered? ie. Range("A1").select Activecell.offset(2,0).select - this selects Range("A3") but is not visible. I want it to consider visible cells only. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select visible cells using vba
Thanks Tom, this is great.
To select a cell further down the filtered range (ie the 2nd visible cell), should I just repeat the do loop function, or is there a better way? In addition to selecting the 2nd visible cell, is there an easy way to copy just this entire row? Thanks again, this is a big help! Cheers, Tony "Tom Ogilvy" wrote: Range("A1").Select do ActiveCell.offset(1,0).Select Loop While ActiveCell.EntireRow.Hidden = True Did you want to select the second visible cell below the current selection rather than the next visible cell? -- Regards, Tom Ogilvy "Tony" wrote in message ... Hi there, Using vba code, I want to select a cell that has been filtered? ie. Range("A1").select Activecell.offset(2,0).select - this selects Range("A3") but is not visible. I want it to consider visible cells only. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select visible cells using vba
1) To continue down the filtered range, use the same method.
If you want to loop throught the visible range and are using an autofilter in this example, copy filtered rows beginning with "ABC" to another sheet Dim cell as Range, rng as Range Dim rng1 as Ragne set rng = Activesheet.Autofilter.Range.Columns(1) set rng = rng.Offset(1,0).Resize(rng.rows.count -1,1) On Error Resume Next set rng1 = rng.specialCells(xlvisible) On Error goto 0 if rng1 is nothing then msgbox "No visible rows" else for each cell in rng1 if left(cell.Value,3) = "ABC" then cell.Entirerow.Copy Destination:= _ Worksheets("Othersheet").Cells(rows.count,1).End(x lup)(2) end if Next End if -------------------- 2) To copy: ActiveCell.Entirerow.copy Destination:= _ Worksheets("Othersheet").Cells(rows.count,1).End(x lup)(2) All code is untested and may contain typos. -- Regards, Tom Ogilvy "Tony" wrote in message ... Thanks Tom, this is great. To select a cell further down the filtered range (ie the 2nd visible cell), should I just repeat the do loop function, or is there a better way? In addition to selecting the 2nd visible cell, is there an easy way to copy just this entire row? Thanks again, this is a big help! Cheers, Tony "Tom Ogilvy" wrote: Range("A1").Select do ActiveCell.offset(1,0).Select Loop While ActiveCell.EntireRow.Hidden = True Did you want to select the second visible cell below the current selection rather than the next visible cell? -- Regards, Tom Ogilvy "Tony" wrote in message ... Hi there, Using vba code, I want to select a cell that has been filtered? ie. Range("A1").select Activecell.offset(2,0).select - this selects Range("A3") but is not visible. I want it to consider visible cells only. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select only used rows and only visible cells | Excel Discussion (Misc queries) | |||
how do I select, cut, and paste visible cells only | Excel Discussion (Misc queries) | |||
Select Visible Cells Only | Excel Discussion (Misc queries) | |||
Trying to select the visible range of cells | Excel Programming | |||
select visible cells when printing | Excel Programming |