Selecting a constant changing Range

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi all

Is it possible to "select" a varying amount of text for
copying from a worksheet (sheet1) that will never be the
same range? I need excel to !find" the extent of the range
for copying every time?

Part of the macro I run copies text from "sheet"1
to "sheet 2", but the number of rows is always different
in sheet 1

Is this possible using VBA?

Thanks for your help.

Regards,

Michael
 
Patrick, much appreciated, works great !
-----Original Message-----
No. The answer you supplied does not set a range.It
merely returns the last row. using a variant type called
MyRange is also misleading in this regard as it infers a
range.

As I understand the questin, the user wants to copy a
table from sheet1 to sheet2, but the table length varies.

In the code below, a table begins at C5, has three
columns and will be copied to sheet2 from cell A1

Sub CopyData()

Dim rSource As Range
Dim rStart As Range

Set rStart = Sheet1.Range("C5")
Set rSource = Sheet1.Range(rStart, rStart.End
(xlDown).Offset(0, 2))

With Sheet2

.Range(.Range("A1"), _
.Cells(rSource.Rows.Count, _
rSource.Columns.Count)).Value = _
rSource.Value

End With

End Sub

first we set memory for two ranges, one for the start
cell (C5) and the other as the source once we determine
the table size...

Set rSource = Sheet1.Range(rStart, rStart.End
(xlDown).Offset(0, 2))

The code above sets the range called rSource to an area
defined by the starting cell , then moves down to the end
of the column and right two columns


Now we have the source range, we know its size from
Columns.Count and Rows.Count, which means we can very
easily copy it anywhere..in the examople , sheet2.


Hope thos is clear.

Patrick Molloy
Microsoft Excel MVP



.
Click to expand...
 
Back
Top