Hi everyone!

I need some assistence to come up with come code that will copy a range of
cells from a single column. The text in the first cell and the last cell are
always the same. But sometimes there could be 5 cells inbetween and other
times there could be 100 cells inbetween. Below is what I ahve come up with
to so far to show where I am trying to go. Thank you for your help!!

startrow = Columns(2).Find("start example 1").Select
ActiveCell.Copy

******Need to also copy all cells inbetween here****

startrow = Columns(2).Find("end example 1").Select
ActiveCell.Copy

Hi,

here's one way :


Sub test()
Dim rStart As Range
Dim rEnd As Range

Set rStart = Columns(2).Find("start example 1")

Set rEnd = Columns(2).Find("end example 1")

If Not rStart Is Nothing And Not rEnd Is Nothing Then
Range(rStart, rEnd).Copy
End If
End Sub



--
Hope that helps.

Vergel Adriano


"BCLivell" wrote:

> Hi everyone!
>
> I need some assistence to come up with come code that will copy a range of
> cells from a single column. The text in the first cell and the last cell are
> always the same. But sometimes there could be 5 cells inbetween and other
> times there could be 100 cells inbetween. Below is what I ahve come up with
> to so far to show where I am trying to go. Thank you for your help!!
>
> startrow = Columns(2).Find("start example 1").Select
> ActiveCell.Copy
>
> ******Need to also copy all cells inbetween here****
>
> startrow = Columns(2).Find("end example 1").Select
> ActiveCell.Copy

Rock on!! thank you!!!!!

"Vergel Adriano" wrote:

> Hi,
>
> here's one way :
>
>
> Sub test()
> Dim rStart As Range
> Dim rEnd As Range
>
> Set rStart = Columns(2).Find("start example 1")
>
> Set rEnd = Columns(2).Find("end example 1")
>
> If Not rStart Is Nothing And Not rEnd Is Nothing Then
> Range(rStart, rEnd).Copy
> End If
> End Sub
>
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "BCLivell" wrote:
>
> > Hi everyone!
> >
> > I need some assistence to come up with come code that will copy a range of
> > cells from a single column. The text in the first cell and the last cell are
> > always the same. But sometimes there could be 5 cells inbetween and other
> > times there could be 100 cells inbetween. Below is what I ahve come up with
> > to so far to show where I am trying to go. Thank you for your help!!
> >
> > startrow = Columns(2).Find("start example 1").Select
> > ActiveCell.Copy
> >
> > ******Need to also copy all cells inbetween here****
> >
> > startrow = Columns(2).Find("end example 1").Select
> > ActiveCell.Copy

hi
try something like this.....

Sub Macro1()
Dim f As Range
Dim l As Range
Set ws = Sheets("Sheet1")
With ws.Range("A1:A10000")
Set f = .Find("firstfind", LookIn:=xlValues)
Set l = .Find("lastfind", LookIn:=xlValues)
Range(f, l).Select
End With
End Sub
edit for your data

Regards
FSt1

"BCLivell" wrote:

> Hi everyone!
>
> I need some assistence to come up with come code that will copy a range of
> cells from a single column. The text in the first cell and the last cell are
> always the same. But sometimes there could be 5 cells inbetween and other
> times there could be 100 cells inbetween. Below is what I ahve come up with
> to so far to show where I am trying to go. Thank you for your help!!
>
> startrow = Columns(2).Find("start example 1").Select
> ActiveCell.Copy
>
> ******Need to also copy all cells inbetween here****
>
> startrow = Columns(2).Find("end example 1").Select
> ActiveCell.Copy

On 8 Jun, 13:58, BCLivell <BCLiv...@discussions.microsoft.com> wrote:
> I need some assistence to come up with come code that will copy a range of
> cells from a single column. The text in the first cell and the last cell are
> always the same. But sometimes there could be 5 cells inbetween and other
> times there could be 100 cells inbetween. Below is what I ahve come up with
> to so far to show where I am trying to go. Thank you for your help!!
>
> startrow = Columns(2).Find("start example 1").Select
> ActiveCell.Copy
>
> ******Need to also copy all cells inbetween here****
>
> startrow = Columns(2).Find("end example 1").Select
> ActiveCell.Copy

How will the range be populated? If it is by the insertion of rows
then you could name the range in the worksheet and just copy it,
regardless of how many rows are added.

Alternatively, do something like this:

Dim rngCopyRange As Range
Dim lngStartRow As Long
Dim lngEndRow As Long
lngStartRow = Columns(2).Find("start example 1").Row
lngEndRow = Columns(2).Find("end example 1").Row
Set rngCopyRange = Range("B" & lngStartRow & ":B" & lngEndRow)
rngCopyRange.Copy

--
juux

On 8 Jun, 15:00, gordon.m...@googlemail.com wrote:
> Dim rngCopyRange As Range
> Dim lngStartRow As Long
> Dim lngEndRow As Long
> lngStartRow = Columns(2).Find("start example 1").Row
> lngEndRow = Columns(2).Find("end example 1").Row
> Set rngCopyRange = Range("B" & lngStartRow & ":B" & lngEndRow)
> rngCopyRange.Copy
>
> --
> juux

...although Vergel's way is more elegant!

--
juux

I forgot to ask about my next step which is pasting that range into another
worksheet starting at cell "a10" in the new worksheet. Thank you!

"Vergel Adriano" wrote:

> Hi,
>
> here's one way :
>
>
> Sub test()
> Dim rStart As Range
> Dim rEnd As Range
>
> Set rStart = Columns(2).Find("start example 1")
>
> Set rEnd = Columns(2).Find("end example 1")
>
> If Not rStart Is Nothing And Not rEnd Is Nothing Then
> Range(rStart, rEnd).Copy
> End If
> End Sub
>
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "BCLivell" wrote:
>
> > Hi everyone!
> >
> > I need some assistence to come up with come code that will copy a range of
> > cells from a single column. The text in the first cell and the last cell are
> > always the same. But sometimes there could be 5 cells inbetween and other
> > times there could be 100 cells inbetween. Below is what I ahve come up with
> > to so far to show where I am trying to go. Thank you for your help!!
> >
> > startrow = Columns(2).Find("start example 1").Select
> > ActiveCell.Copy
> >
> > ******Need to also copy all cells inbetween here****
> >
> > startrow = Columns(2).Find("end example 1").Select
> > ActiveCell.Copy

try replacing this line

Range(rStart, rEnd).Copy

with this one

Range(rStart, rEnd).Copy ThisWorkbook.Worksheets.Add.Range("A10")


... if the destination is on a sheet that already exists, for example, a
sheet named "Sheet2", then, you can do it this way:

Range(rStart, rEnd).Copy ThisWorkbook.Worksheets("Sheet2").Range("A10")


--
Hope that helps.

Vergel Adriano


"BCLivell" wrote:

> I forgot to ask about my next step which is pasting that range into another
> worksheet starting at cell "a10" in the new worksheet. Thank you!
>
> "Vergel Adriano" wrote:
>
> > Hi,
> >
> > here's one way :
> >
> >
> > Sub test()
> > Dim rStart As Range
> > Dim rEnd As Range
> >
> > Set rStart = Columns(2).Find("start example 1")
> >
> > Set rEnd = Columns(2).Find("end example 1")
> >
> > If Not rStart Is Nothing And Not rEnd Is Nothing Then
> > Range(rStart, rEnd).Copy
> > End If
> > End Sub
> >
> >
> >
> > --
> > Hope that helps.
> >
> > Vergel Adriano
> >
> >
> > "BCLivell" wrote:
> >
> > > Hi everyone!
> > >
> > > I need some assistence to come up with come code that will copy a range of
> > > cells from a single column. The text in the first cell and the last cell are
> > > always the same. But sometimes there could be 5 cells inbetween and other
> > > times there could be 100 cells inbetween. Below is what I ahve come up with
> > > to so far to show where I am trying to go. Thank you for your help!!
> > >
> > > startrow = Columns(2).Find("start example 1").Select
> > > ActiveCell.Copy
> > >
> > > ******Need to also copy all cells inbetween here****
> > >
> > > startrow = Columns(2).Find("end example 1").Select
> > > ActiveCell.Copy

Thank you!!

"Vergel Adriano" wrote:

> try replacing this line
>
> Range(rStart, rEnd).Copy
>
> with this one
>
> Range(rStart, rEnd).Copy ThisWorkbook.Worksheets.Add.Range("A10")
>
>
> .. if the destination is on a sheet that already exists, for example, a
> sheet named "Sheet2", then, you can do it this way:
>
> Range(rStart, rEnd).Copy ThisWorkbook.Worksheets("Sheet2").Range("A10")
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "BCLivell" wrote:
>
> > I forgot to ask about my next step which is pasting that range into another
> > worksheet starting at cell "a10" in the new worksheet. Thank you!
> >
> > "Vergel Adriano" wrote:
> >
> > > Hi,
> > >
> > > here's one way :
> > >
> > >
> > > Sub test()
> > > Dim rStart As Range
> > > Dim rEnd As Range
> > >
> > > Set rStart = Columns(2).Find("start example 1")
> > >
> > > Set rEnd = Columns(2).Find("end example 1")
> > >
> > > If Not rStart Is Nothing And Not rEnd Is Nothing Then
> > > Range(rStart, rEnd).Copy
> > > End If
> > > End Sub
> > >
> > >
> > >
> > > --
> > > Hope that helps.
> > >
> > > Vergel Adriano
> > >
> > >
> > > "BCLivell" wrote:
> > >
> > > > Hi everyone!
> > > >
> > > > I need some assistence to come up with come code that will copy a range of
> > > > cells from a single column. The text in the first cell and the last cell are
> > > > always the same. But sometimes there could be 5 cells inbetween and other
> > > > times there could be 100 cells inbetween. Below is what I ahve come up with
> > > > to so far to show where I am trying to go. Thank you for your help!!
> > > >
> > > > startrow = Columns(2).Find("start example 1").Select
> > > > ActiveCell.Copy
> > > >
> > > > ******Need to also copy all cells inbetween here****
> > > >
> > > > startrow = Columns(2).Find("end example 1").Select
> > > > ActiveCell.Copy