Macro to Specific Cells in a Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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.
 
Got it! Thanks a million Tom!

Tom Ogilvy said:
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.
Click to expand...
 
Bob, thanks a lot for your help!

Bob Phillips said:
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)
Click to expand...
 
Back
Top