defining a variable-size worksheet area for copying & pasting
 

The worksheets I work with have a variable number of columns and rows, thus
when I record a macro and use a Ctrl-Shift-End to highlight an area to copy,
it may/will not work if re-used with a different set of data. This is
because the macro records an absolute address of the lowest rightmost cell,
not the Ctrl-Shift-End operation. Of course, I could always oversize the
area being copied to the largest possible size, but the solution would not
be elegant and probably waste storage space...

What expression/formula should this lowest right address be replaced with in
a modified macro to include only the rectangular area with filled cells?
What if the upper left cell's address is other than A1?

z.entropic

defining a variable-size worksheet area for copying & pasting
 

Does this do what you need?

Sub Ender()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
End Sub



--
RyGuy


"z.entropic" wrote:

The worksheets I work with have a variable number of columns and rows, thus
when I record a macro and use a Ctrl-Shift-End to highlight an area to copy,
it may/will not work if re-used with a different set of data. This is
because the macro records an absolute address of the lowest rightmost cell,
not the Ctrl-Shift-End operation. Of course, I could always oversize the
area being copied to the largest possible size, but the solution would not
be elegant and probably waste storage space...

What expression/formula should this lowest right address be replaced with in
a modified macro to include only the rectangular area with filled cells?
What if the upper left cell's address is other than A1?

z.entropic

defining a variable-size worksheet area for copying & pasting
 

"ryguy7272" wrote:

Does this do what you need?

Sub Ender()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
End Sub



--
RyGuy


"z.entropic" wrote:

The worksheets I work with have a variable number of columns and rows, thus
when I record a macro and use a Ctrl-Shift-End to highlight an area to copy,
it may/will not work if re-used with a different set of data. This is
because the macro records an absolute address of the lowest rightmost cell,
not the Ctrl-Shift-End operation. Of course, I could always oversize the
area being copied to the largest possible size, but the solution would not
be elegant and probably waste storage space...

What expression/formula should this lowest right address be replaced with in
a modified macro to include only the rectangular area with filled cells?
What if the upper left cell's address is other than A1?

z.entropic

defining a variable-size worksheet area for copying & pasting
 

Thanks, it does--partially. However, I'm not sure how to replace the B15:I52
address in:

Sheets("Data").Select
Range("B15").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Data").Range("B15:I52")
ActiveChart.Location Whe=xlLocationAsObject, Name:="Data"

z.entropic

"ryguy7272" wrote:

Does this do what you need?

Sub Ender()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
End Sub



--
RyGuy


"z.entropic" wrote:

The worksheets I work with have a variable number of columns and rows, thus
when I record a macro and use a Ctrl-Shift-End to highlight an area to copy,
it may/will not work if re-used with a different set of data. This is
because the macro records an absolute address of the lowest rightmost cell,
not the Ctrl-Shift-End operation. Of course, I could always oversize the
area being copied to the largest possible size, but the solution would not
be elegant and probably waste storage space...

What expression/formula should this lowest right address be replaced with in
a modified macro to include only the rectangular area with filled cells?
What if the upper left cell's address is other than A1?

z.entropic