LinkBack URL
About LinkBacksSorry try this...
Sub SelectNonBlanks()
Dim rng As Range
Dim rngConstants As Range
Dim rngFormulas As Range
With Sheets("Sheet1").Columns(1)
On Error Resume Next
Set rngConstants = .SpecialCells(xlCellTypeConstants)
Set rngFormulas = .SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rngConstants Is Nothing And Not rngFormulas Is Nothing Then
Set rng = Union(rngConstants, rngFormulas)
ElseIf rngConstants Is Nothing Then
Set rng = rngFormulas
ElseIf rngFormulas Is Nothing Then
Set rng = rngConstants
End If
End With
rng.Select
MsgBox "Tada"
Set rng = rng.Offset(0, 2)
rng.Select
MsgBox "Just like Magic"
End Sub
--
HTH...
Jim Thomlinson
"achidsey" wrote:
> This message is from the person who originally posted the question.
>
> I appreciate the post in response to my question, but when I tried to run
> this code I got the following error:
>
> Run time error 1004
> No cells were found
>
> Jim, if you see this, do you know why I might have this problem?
>
> Thanks,
> Alan
>
>
> --
> achidsey
>
>
> "Jim Thomlinson" wrote:
>
> > Sub SelectNonBlanks()
> > Dim rng As Range
> >
> > With Sheets("Sheet1").Columns(1)
> > Set rng = Union(.SpecialCells(xlConstants), .SpecialCells(xlFormulas))
> > End With
> > rng.Select
> > MsgBox "Tada"
> > Set rng = rng.Offset(0, 2)
> > rng.Select
> > MsgBox "Just like Magic"
> >
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "achidsey" wrote:
> >
> > > Hi Friends,
> > > I'm trying to select a group of cells in column C based on those in column A
> > > which are not blank. I'm then going to put a formula in each of these cells
> > > in column C.
> > >
> > > My spreadsheet is as follows:
> > >
> > > A B C D
> > > 1 Sym Quant Quant2
> > > 2 AMD 50
> > > 3 CSCO 200
> > > 4 DELL 150
> > > 5
> > > 6 IBM 70
> > > 7 HWP 300
> > >
> > > In my VBA code, I want to put a formula in cells C2, C3, C4, C6, C7. That
> > > is, all the cells in column C for which there is a symbol in column A. I
> > > don't want to put a formula in C5 because there is no symbol in A5.
> > >
> > > When I've done this based on a range with no blank cells, I've written
> > > something similiar to the following:
> > >
> > > 'Create Range "Symbol"
> > > Range("A1").Select
> > > Cells.Find(What:="Sym").Range("A2").Select
> > > Set FirstSymbol = Selection
> > >
> > > Selection.Range("A5000").Select
> > > Selection.End(xlUp).Select
> > > Set LastSymbol = Selection
> > > Range(FirstSymbol, LastSymbol).Select
> > > Set Selection = Symbol
> > >
> > > That creates the range in column A. I would then put in the code to select
> > > a similar range as "Symbol" but in column C.
> > >
> > > What I want to know is how I can select cells A2 through A7 but exclude A5.
> > >
> > > Thanks,
> > > Alan
> > > chidsey2@hotmail.com
> > >
> > > --
> > > achidsey
Register To Reply
Bookmarks