Selecting First and Last Cells in Ranges

[Guest]

Hello,

I have a macro that selects all cells in one row for which cells in another
row are nonblanks. For example, suppose that in Sheet1, ranges C1:R1 and
U1:Z1 contain values, and the other cells in that row are blanks. The macro
selects the corresponding cells in row 15, namely C15:R15 and U15:Z15. This
macro then performs an operation on these cells. The problem is that I need
the macro to perform a slightly different operation on the first and last
cells in each of these ranges, namely cells C15,R15,U15, and Z15. I'm having
trouble coming up with a macro that would do that. Any assistance that you
could provide would be tremendously appreciated.

Sincerely,

Magnivy

 

[Tom Ogilvy]

Dim rng as Range, rng1 as Range, ar as Range
Dim lastcell as Range
set rng = rows(1).SpecialCells(xlConstants)
set rng1 = Intersect(rows(15),rng.EntireColumn)
for each ar in rng1.Areas
set lastcell = area(ar.count)
msgbox ar(1).Address & " - " & lastcell.Address

Next

 

[Guest]

Tom,

Thanks a lot for your help. When I run a macro, it creates a Compile Error,
saying that "The Sub or Function not defined," and the "area" in the line
"set lastcell = area(ar.count)" is highlighted. Please advise how to
overcome this.

Thank you!

Magnivy

 

[Tom Ogilvy]

sorry, changed the variable name midstride:

Dim rng as Range, rng1 as Range, ar as Range
Dim lastcell as Range
set rng = rows(1).SpecialCells(xlConstants)
set rng1 = Intersect(rows(15),rng.EntireColumn)
for each ar in rng1.Areas
set lastcell = ar(ar.count)
msgbox ar(1).Address & " - " & lastcell.Address
Next

 

[Guest]

Tom, Thank you vry much for your help again! I'm sorry to bother your again
but instead of having the macro create messege boxes with the ranges, is it
possible to have it select the first and last cell of each one of the ranges
in row 15?

 

[Guest]

Sub AABBCC()
Dim rng As Range, rng1 As Range, ar As Range
Dim lastcell As Range, rng2 As Range
Set rng = Rows(1).SpecialCells(xlConstants)
Set rng1 = Intersect(Rows(15), rng.EntireColumn)
For Each ar In rng1.Areas
Set lastcell = ar(ar.Count)
If rng2 Is Nothing Then
Set rng2 = Union(ar(1), lastcell)
Else
Set rng2 = Union(rng2, ar(1), lastcell)
End If
Next
rng2.Select
End Sub

 

[Guest]

Got it! Thank you VERY much Tom!

Sub AABBCC()
Dim rng As Range, rng1 As Range, ar As Range
Dim lastcell As Range, rng2 As Range
Set rng = Rows(1).SpecialCells(xlConstants)
Set rng1 = Intersect(Rows(15), rng.EntireColumn)
For Each ar In rng1.Areas
Set lastcell = ar(ar.Count)
If rng2 Is Nothing Then
Set rng2 = Union(ar(1), lastcell)
Else
Set rng2 = Union(rng2, ar(1), lastcell)
End If
Next
rng2.Select
End Sub