Macro to Select 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

 

[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.