| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
special cell selection
Hello. I hope someone can help me with this please.
I would like to be able to select the visible cells in the used range of column "B" in a sheet. Ideally, it would do so in a single line of VBA code. Your example code would be most appreciated. Thanks in advance. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
special cell selection
NEVERMIND. I figured it out (duh):
Application.Intersect(Range("B:B") _ .SpecialCells(xlCellTypeVisible), _ Range(ActiveSheet.UsedRange.Address)).Select -----Original Message----- Hello. I hope someone can help me with this please. I would like to be able to select the visible cells in the used range of column "B" in a sheet. Ideally, it would do so in a single line of VBA code. Your example code would be most appreciated. Thanks in advance. . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
special cell selection
On Error Resume Next
Columns(2).SpecialCells(xlVisible).Select On Error goto 0 might be a bit simpler. Special cells restricts itself to the usedrange, so you don't need to do the intersect. -- Regards, Tom Ogilvy raj wrote in message ... NEVERMIND. I figured it out (duh): Application.Intersect(Range("B:B") _ .SpecialCells(xlCellTypeVisible), _ Range(ActiveSheet.UsedRange.Address)).Select -----Original Message----- Hello. I hope someone can help me with this please. I would like to be able to select the visible cells in the used range of column "B" in a sheet. Ideally, it would do so in a single line of VBA code. Your example code would be most appreciated. Thanks in advance. . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
special cell selection
or
With ActiveSheet Intersect(.Range("b:b").SpecialCells(xlCellTypeVis ible), _ .UsedRange).Select End With (and watch out. if there are no visible cells, then you'll get an error.) raj wrote: NEVERMIND. I figured it out (duh): Application.Intersect(Range("B:B") _ .SpecialCells(xlCellTypeVisible), _ Range(ActiveSheet.UsedRange.Address)).Select -----Original Message----- Hello. I hope someone can help me with this please. I would like to be able to select the visible cells in the used range of column "B" in a sheet. Ideally, it would do so in a single line of VBA code. Your example code would be most appreciated. Thanks in advance. . -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
special cell selection
UsedRange, of course, is redundant.
-- Regards, Tom Ogilvy Dave Peterson wrote in message ... or With ActiveSheet Intersect(.Range("b:b").SpecialCells(xlCellTypeVis ible), _ .UsedRange).Select End With (and watch out. if there are no visible cells, then you'll get an error.) raj wrote: NEVERMIND. I figured it out (duh): Application.Intersect(Range("B:B") _ .SpecialCells(xlCellTypeVisible), _ Range(ActiveSheet.UsedRange.Address)).Select -----Original Message----- Hello. I hope someone can help me with this please. I would like to be able to select the visible cells in the used range of column "B" in a sheet. Ideally, it would do so in a single line of VBA code. Your example code would be most appreciated. Thanks in advance. . -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
special cell selection
Yep.
I saw your response and said "doh". I hope Tom doesn't read mine. Tom Ogilvy wrote: UsedRange, of course, is redundant. -- Regards, Tom Ogilvy Dave Peterson wrote in message ... or With ActiveSheet Intersect(.Range("b:b").SpecialCells(xlCellTypeVis ible), _ .UsedRange).Select End With (and watch out. if there are no visible cells, then you'll get an error.) raj wrote: NEVERMIND. I figured it out (duh): Application.Intersect(Range("B:B") _ .SpecialCells(xlCellTypeVisible), _ Range(ActiveSheet.UsedRange.Address)).Select -----Original Message----- Hello. I hope someone can help me with this please. I would like to be able to select the visible cells in the used range of column "B" in a sheet. Ideally, it would do so in a single line of VBA code. Your example code would be most appreciated. Thanks in advance. . -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
special cell selection
An interesting note (well to me anyway <g) --
If any cell outside the used range is currently selected, your code selects just the visible cells in the the used range in column B, and Tom's selects all visible cells in column B. Dave Peterson wrote: Yep. I saw your response and said "doh". I hope Tom doesn't read mine. Tom Ogilvy wrote: UsedRange, of course, is redundant. -- Regards, Tom Ogilvy Dave Peterson wrote in message ... or With ActiveSheet Intersect(.Range("b:b").SpecialCells(xlCellTypeVis ible), _ .UsedRange).Select End With (and watch out. if there are no visible cells, then you'll get an error.) raj wrote: NEVERMIND. I figured it out (duh): Application.Intersect(Range("B:B") _ .SpecialCells(xlCellTypeVisible), _ Range(ActiveSheet.UsedRange.Address)).Select -----Original Message----- Hello. I hope someone can help me with this please. I would like to be able to select the visible cells in the used range of column "B" in a sheet. Ideally, it would do so in a single line of VBA code. Your example code would be most appreciated. Thanks in advance. . -- Dave Peterson -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
special cell selection
I believe I am wrong on this - so my apologies to Dave and the OP.
Anyway On Error Resume Next set rng = Activesheet.UsedRange.Columns(2).Specialcells(xlVi sible) On Error goto 0 although this assumes column A is in the used range. -- Regards, Tom Ogilvy Debra Dalgleish wrote in message ... An interesting note (well to me anyway <g) -- If any cell outside the used range is currently selected, your code selects just the visible cells in the the used range in column B, and Tom's selects all visible cells in column B. Dave Peterson wrote: Yep. I saw your response and said "doh". I hope Tom doesn't read mine. Tom Ogilvy wrote: UsedRange, of course, is redundant. -- Regards, Tom Ogilvy Dave Peterson wrote in message ... or With ActiveSheet Intersect(.Range("b:b").SpecialCells(xlCellTypeVis ible), _ .UsedRange).Select End With (and watch out. if there are no visible cells, then you'll get an error.) raj wrote: NEVERMIND. I figured it out (duh): Application.Intersect(Range("B:B") _ .SpecialCells(xlCellTypeVisible), _ Range(ActiveSheet.UsedRange.Address)).Select -----Original Message----- Hello. I hope someone can help me with this please. I would like to be able to select the visible cells in the used range of column "B" in a sheet. Ideally, it would do so in a single line of VBA code. Your example code would be most appreciated. Thanks in advance. . -- Dave Peterson -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Limiting selection in a cell AND linking that selection to a list | Excel Discussion (Misc queries) | |||
| How flip a selection before pasting - Paste special | Excel Discussion (Misc queries) | |||
| In Excel: Paste Special should have a "Link Value" Selection | Excel Worksheet Functions | |||
| limit cell list selection based on the selection of another list | Excel Worksheet Functions | |||
| Delete all special characters in a selection? | Excel Programming | |||
Linear Mode
