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


--
achidsey