Hi

Could someone please advise on a simple way of selecting a variable area
from a workbook, for example for copying, as it keeps croping up in various
tasks?

In the application I am looking at now the number of columns is fixed at 21
('U') but the number of rows will change. Ideally I'll select the first cell
ie A1 and then run a macro which will count the number of rows which contain
text (other than spaces)to work out the range required by combining with the
number of columns and then select the entire range ready for copying and
pasting. Of course a more flexible version which also automatically worked
out the number of columns would be of interest.

Thanks for any help

G

If there is always data in all cells, or a particular cell in a used row then
this would work to find the last row used (although there could be blank rows
above it). In this example, I assume that Column A will always have data in
it for any "row of interest".

Dim LastRowUsed As Long
Dim rngToCopy as Range

LastRowUsed = Range("A" & Rows.Count).End(xlUp).Row

Set rngToCopy = "A1:U" & LastRowUsed

You're now ready to use rngToCopy as the source of a copy to another location.
"Constantly Amazed" wrote:

Hi

Could someone please advise on a simple way of selecting a variable area
from a workbook, for example for copying, as it keeps croping up in various
tasks?

In the application I am looking at now the number of columns is fixed at 21
('U') but the number of rows will change. Ideally I'll select the first cell
ie A1 and then run a macro which will count the number of rows which contain
text (other than spaces)to work out the range required by combining with the
number of columns and then select the entire range ready for copying and
pasting. Of course a more flexible version which also automatically worked
out the number of columns would be of interest.

Thanks for any help

G

If you are after all the data on a worksheet, then

Activesheet.usedrange.copy

will copy the used range. As long as you have data in cell A1 or in both
row 1 and column A, this will pick up A1.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


"Constantly Amazed" wrote in
message ...
Hi

Could someone please advise on a simple way of selecting a variable area
from a workbook, for example for copying, as it keeps croping up in
various
tasks?

In the application I am looking at now the number of columns is fixed at
21
('U') but the number of rows will change. Ideally I'll select the first
cell
ie A1 and then run a macro which will count the number of rows which
contain
text (other than spaces)to work out the range required by combining with
the
number of columns and then select the entire range ready for copying and
pasting. Of course a more flexible version which also automatically
worked
out the number of columns would be of interest.

Thanks for any help

G

Hi

You can get the extent of the used range (first to last column and first to
last row)

e.g.....

Dim myrange As Range

Set myrange = ActiveSheet.UsedRange

myrange.Select



"Constantly Amazed" wrote:

Hi

Could someone please advise on a simple way of selecting a variable area
from a workbook, for example for copying, as it keeps croping up in various
tasks?

In the application I am looking at now the number of columns is fixed at 21
('U') but the number of rows will change. Ideally I'll select the first cell
ie A1 and then run a macro which will count the number of rows which contain
text (other than spaces)to work out the range required by combining with the
number of columns and then select the entire range ready for copying and
pasting. Of course a more flexible version which also automatically worked
out the number of columns would be of interest.

Thanks for any help

G

I did not recommend using the .UsedRange property because I didn't think you
wanted the possibility of copying empty rows - and with .UsedRange there is
that potential. But in reality, since you're only dealing with a maximum of
21 rows, it's probably a good, quick solution.

UsedRange will, without taking other steps, always point to the right-most
column and largest row number ever used on a sheet. So if you ever entered
anything into Z99, even if you only had data in A1, if you used .UsedRange to
select for copying, it would pick up all cells from A1:Z99 - just so you'll
know.

"JLatham" wrote:

If there is always data in all cells, or a particular cell in a used row then
this would work to find the last row used (although there could be blank rows
above it). In this example, I assume that Column A will always have data in
it for any "row of interest".

Dim LastRowUsed As Long
Dim rngToCopy as Range

LastRowUsed = Range("A" & Rows.Count).End(xlUp).Row

Set rngToCopy = "A1:U" & LastRowUsed

You're now ready to use rngToCopy as the source of a copy to another location.
"Constantly Amazed" wrote:

Hi

Could someone please advise on a simple way of selecting a variable area
from a workbook, for example for copying, as it keeps croping up in various
tasks?

In the application I am looking at now the number of columns is fixed at 21
('U') but the number of rows will change. Ideally I'll select the first cell
ie A1 and then run a macro which will count the number of rows which contain
text (other than spaces)to work out the range required by combining with the
number of columns and then select the entire range ready for copying and
pasting. Of course a more flexible version which also automatically worked
out the number of columns would be of interest.

Thanks for any help

G

Hi JLatham

In several cases the active area could be larger than the area I need to
copy. For example in producing a template to manipulate data from suppliers
as I do not know how many lines there are in their catalogue I created a 1000
line template but there may only be, say, 100 lines of products. Therefore I
only want to select the 100 lines where there is text not the other 900
containing formulas.

G

"JLatham" wrote:

I did not recommend using the .UsedRange property because I didn't think you
wanted the possibility of copying empty rows - and with .UsedRange there is
that potential. But in reality, since you're only dealing with a maximum of
21 rows, it's probably a good, quick solution.

UsedRange will, without taking other steps, always point to the right-most
column and largest row number ever used on a sheet. So if you ever entered
anything into Z99, even if you only had data in A1, if you used .UsedRange to
select for copying, it would pick up all cells from A1:Z99 - just so you'll
know.

"JLatham" wrote:

If there is always data in all cells, or a particular cell in a used row then
this would work to find the last row used (although there could be blank rows
above it). In this example, I assume that Column A will always have data in
it for any "row of interest".

Dim LastRowUsed As Long
Dim rngToCopy as Range

LastRowUsed = Range("A" & Rows.Count).End(xlUp).Row

Set rngToCopy = "A1:U" & LastRowUsed

You're now ready to use rngToCopy as the source of a copy to another location.
"Constantly Amazed" wrote:

Hi

Could someone please advise on a simple way of selecting a variable area
from a workbook, for example for copying, as it keeps croping up in various
tasks?

In the application I am looking at now the number of columns is fixed at 21
('U') but the number of rows will change. Ideally I'll select the first cell
ie A1 and then run a macro which will count the number of rows which contain
text (other than spaces)to work out the range required by combining with the
number of columns and then select the entire range ready for copying and
pasting. Of course a more flexible version which also automatically worked
out the number of columns would be of interest.

Thanks for any help

G

Then the method I offered is going to come closest to grabbing only what is
actually used. The code below also gets smarter about what columns are used.

Sub CopyRange()
Dim lastRow As Long
Dim lastCol As Long
Dim rngToCopy As Range
Dim destinationRange As Range

'pick a column that will always have entries down to
'last row used
lastRow = Range("A" & Rows.Count).End(xlUp).Row
'pick a row that will always have entries in all
'used columns (as a header row)
lastCol = Range("IV1").End(xlToLeft).Column
Set rngToCopy = Range("A1:" & _
Range("A1").Offset(lastRow - 1, lastCol - 1).Address)
'pick upperleft corner to paste data into
'here we want to paste starting at B3 instead of A1
Set destinationRange = _
Worksheets("newsheet").Range("A3:" & _
Range("A3").Offset(lastRow - 1, lastCol - 1).Address)
destinationRange.Value = rngToCopy.Value
Set rngToCopy = Nothing
Set destinationRange = Nothing

End Sub


"Constantly Amazed" wrote:

Hi JLatham

In several cases the active area could be larger than the area I need to
copy. For example in producing a template to manipulate data from suppliers
as I do not know how many lines there are in their catalogue I created a 1000
line template but there may only be, say, 100 lines of products. Therefore I
only want to select the 100 lines where there is text not the other 900
containing formulas.

G

"JLatham" wrote:

I did not recommend using the .UsedRange property because I didn't think you
wanted the possibility of copying empty rows - and with .UsedRange there is
that potential. But in reality, since you're only dealing with a maximum of
21 rows, it's probably a good, quick solution.

UsedRange will, without taking other steps, always point to the right-most
column and largest row number ever used on a sheet. So if you ever entered
anything into Z99, even if you only had data in A1, if you used .UsedRange to
select for copying, it would pick up all cells from A1:Z99 - just so you'll
know.

"JLatham" wrote:

If there is always data in all cells, or a particular cell in a used row then
this would work to find the last row used (although there could be blank rows
above it). In this example, I assume that Column A will always have data in
it for any "row of interest".

Dim LastRowUsed As Long
Dim rngToCopy as Range

LastRowUsed = Range("A" & Rows.Count).End(xlUp).Row

Set rngToCopy = "A1:U" & LastRowUsed

You're now ready to use rngToCopy as the source of a copy to another location.
"Constantly Amazed" wrote:

Hi

Could someone please advise on a simple way of selecting a variable area
from a workbook, for example for copying, as it keeps croping up in various
tasks?

In the application I am looking at now the number of columns is fixed at 21
('U') but the number of rows will change. Ideally I'll select the first cell
ie A1 and then run a macro which will count the number of rows which contain
text (other than spaces)to work out the range required by combining with the
number of columns and then select the entire range ready for copying and
pasting. Of course a more flexible version which also automatically worked
out the number of columns would be of interest.

Thanks for any help

G