Exclude blank cells from a range?

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

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

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

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

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

Great Jim, thanks much for the followup.

Alan


--
achidsey


"Jim Thomlinson" wrote:

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