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

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" <Magnivy@discussions.microsoft.com> wrote in message
news:6616311C-FB89-4FF3-B255-A14BD95C5008@microsoft.com...
> 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,

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" <Magnivy@discussions.microsoft.com> wrote in message
> news:6616311C-FB89-4FF3-B255-A14BD95C5008@microsoft.com...
> > 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
> >
>
>
>

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" <Magnivy@discussions.microsoft.com> wrote in message
news:8C0E1E80-3A7E-40B2-9246-D1EBADFD9BBB@microsoft.com...
> 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" <Magnivy@discussions.microsoft.com> wrote in message
> > news:6616311C-FB89-4FF3-B255-A14BD95C5008@microsoft.com...
> > > 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, 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" <Magnivy@discussions.microsoft.com> wrote in message
> news:8C0E1E80-3A7E-40B2-9246-D1EBADFD9BBB@microsoft.com...
> > 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" <Magnivy@discussions.microsoft.com> wrote in message
> > > news:6616311C-FB89-4FF3-B255-A14BD95C5008@microsoft.com...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>

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" <Magnivy@discussions.microsoft.com> wrote in message
> > news:8C0E1E80-3A7E-40B2-9246-D1EBADFD9BBB@microsoft.com...
> > > 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" <Magnivy@discussions.microsoft.com> wrote in message
> > > > news:6616311C-FB89-4FF3-B255-A14BD95C5008@microsoft.com...
> > > > > 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
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <Magnivy@discussions.microsoft.com> wrote in message
> > > news:8C0E1E80-3A7E-40B2-9246-D1EBADFD9BBB@microsoft.com...
> > > > 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" <Magnivy@discussions.microsoft.com> wrote in message
> > > > > news:6616311C-FB89-4FF3-B255-A14BD95C5008@microsoft.com...
> > > > > > 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
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >