Hello,

I am trying to create a macro that would select specific cells within a
horizontal vector, so that I could perform an operation only on the selected
cells. For example, I have two horizontal ranges, Range 1 is A1:V1 and Range
2 is A12:V12. I would like to create a macro that would select the cells in
Range 2 only for which the corresponding cells in Range 1 are non blanks, so
that I could perform another operation on cells in Range 2 for which the
corresponding cells in Range 1 are non blanks. Any assistance with this would
be greatly appreciated!

Magnivy

Sub selectcells()
Dim rng1 As Range
Dim rng2 As Range
Dim rng As Range

On Error Resume Next
Set rng1 = Range("A1:V1").SpecialCells(xlCellTypeConstants)
Set rng2 = Range("A1:V1").SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng1 Is Nothing And rng2 Is Nothing Then
MsgBox "No blank cells"
Else
Set rng = rng1
If rng1 Is Nothing Then
Set rng = rng2
ElseIf rng2 Is Nothing Then
Set rng = rng1
Else
Set rng = Union(rng1, rng2)
End If
Intersect(rng.EntireColumn, Range("A12:V12")).Select
End If


End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Magnivy" wrote in message
...
Hello,

I am trying to create a macro that would select specific cells within a
horizontal vector, so that I could perform an operation only on the
selected
cells. For example, I have two horizontal ranges, Range 1 is A1:V1 and
Range
2 is A12:V12. I would like to create a macro that would select the cells
in
Range 2 only for which the corresponding cells in Range 1 are non blanks,
so
that I could perform another operation on cells in Range 2 for which the
corresponding cells in Range 1 are non blanks. Any assistance with this
would
be greatly appreciated!

Magnivy

Sub SelectRange()
dim cell as range, rng as Range
dim rng1 as Range, rng2 as Range
Dim rng3 as Range

set rng = Range("A1:V1")
for each cell in rng
if not isempty(cell) then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng,cell)
end if
end if
Next
If not rng1 is nothing then
set rng2 = range("A12:V12")
set rng3 = intersect(rng1.entireColumn,rng2)
rng3.Select
end if
end Sub

If I know if the non empty cells contained constants or formulas
exclusively, you could use specialcells the occupied cells in your range.
Since I don't, it is just as easy to loop over such a small range and check.

--
Regards,
Tom Ogilvy


"Magnivy" wrote:

Hello,

I am trying to create a macro that would select specific cells within a
horizontal vector, so that I could perform an operation only on the selected
cells. For example, I have two horizontal ranges, Range 1 is A1:V1 and Range
2 is A12:V12. I would like to create a macro that would select the cells in
Range 2 only for which the corresponding cells in Range 1 are non blanks, so
that I could perform another operation on cells in Range 2 for which the
corresponding cells in Range 1 are non blanks. Any assistance with this would
be greatly appreciated!

Magnivy

Got it! Thanks a million Tom!

"Tom Ogilvy" wrote:

Sub SelectRange()
dim cell as range, rng as Range
dim rng1 as Range, rng2 as Range
Dim rng3 as Range

set rng = Range("A1:V1")
for each cell in rng
if not isempty(cell) then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng,cell)
end if
end if
Next
If not rng1 is nothing then
set rng2 = range("A12:V12")
set rng3 = intersect(rng1.entireColumn,rng2)
rng3.Select
end if
end Sub

If I know if the non empty cells contained constants or formulas
exclusively, you could use specialcells the occupied cells in your range.
Since I don't, it is just as easy to loop over such a small range and check.

--
Regards,
Tom Ogilvy


"Magnivy" wrote:

Hello,

I am trying to create a macro that would select specific cells within a
horizontal vector, so that I could perform an operation only on the selected
cells. For example, I have two horizontal ranges, Range 1 is A1:V1 and Range
2 is A12:V12. I would like to create a macro that would select the cells in
Range 2 only for which the corresponding cells in Range 1 are non blanks, so
that I could perform another operation on cells in Range 2 for which the
corresponding cells in Range 1 are non blanks. Any assistance with this would
be greatly appreciated!

Magnivy

Bob, thanks a lot for your help!

"Bob Phillips" wrote:

Sub selectcells()
Dim rng1 As Range
Dim rng2 As Range
Dim rng As Range

On Error Resume Next
Set rng1 = Range("A1:V1").SpecialCells(xlCellTypeConstants)
Set rng2 = Range("A1:V1").SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng1 Is Nothing And rng2 Is Nothing Then
MsgBox "No blank cells"
Else
Set rng = rng1
If rng1 Is Nothing Then
Set rng = rng2
ElseIf rng2 Is Nothing Then
Set rng = rng1
Else
Set rng = Union(rng1, rng2)
End If
Intersect(rng.EntireColumn, Range("A12:V12")).Select
End If


End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Magnivy" wrote in message
...
Hello,

I am trying to create a macro that would select specific cells within a
horizontal vector, so that I could perform an operation only on the
selected
cells. For example, I have two horizontal ranges, Range 1 is A1:V1 and
Range
2 is A12:V12. I would like to create a macro that would select the cells
in
Range 2 only for which the corresponding cells in Range 1 are non blanks,
so
that I could perform another operation on cells in Range 2 for which the
corresponding cells in Range 1 are non blanks. Any assistance with this
would
be greatly appreciated!

Magnivy