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
> [email protected]
>
> --
> achidsey