Macro to Specific Cells in a Range

[Guest]

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 Phillips]

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)

 

[Guest]

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.

 

[Guest]

Got it! Thanks a million Tom!

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.

 

[Guest]

Bob, thanks a lot for your help!

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)