Exclude blank cells from a range?

[Guest]

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
(e-mail address removed)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Sorry 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

 

[Guest]

Great Jim, thanks much for the followup.

Alan