Selecting First and Last Cells in Ranges
 

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

Selecting First and Last Cells in Ranges
 

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

--
Regards,
Tom Ogilvy


"Magnivy" wrote in message
...
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

Selecting First and Last Cells in Ranges
 

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" wrote:

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

--
Regards,
Tom Ogilvy


"Magnivy" wrote in message
...
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

Selecting First and Last Cells in Ranges
 

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

--
Regards,
Tom Ogilvy

"Magnivy" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Magnivy" wrote in message
...
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

Selecting First and Last Cells in Ranges
 

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?

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Magnivy" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Magnivy" wrote in message
...
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

Selecting First and Last Cells in Ranges
 

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


--
Regards,
Tom Ogilvy


"Magnivy" wrote:

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?

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Magnivy" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Magnivy" wrote in message
...
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

Selecting First and Last Cells in Ranges
 

Got it! Thank you VERY much Tom!

"Tom Ogilvy" wrote:

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


--
Regards,
Tom Ogilvy


"Magnivy" wrote:

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?

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Magnivy" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"Magnivy" wrote in message
...
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