Copy cells that vary in range
 

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

Copy cells that vary in range
 

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

Copy cells that vary in range
 

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

Copy cells that vary in range
 

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

Copy cells that vary in range
 

On 8 Jun, 13:58, BCLivell 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

Copy cells that vary in range
 

On 8 Jun, 15:00, 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

Copy cells that vary in range
 

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

Copy cells that vary in range
 

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

Copy cells that vary in range
 

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