Resize Range - Macro
 

Hi,

Please write a macro to CHANGE number of rows €œ3€� to a variable range
(end.xlDown) on the macro below.

Sub GotToRangeResizeCopyPaste()

Application.Goto Reference:="MyRange"
ActiveCell.Resize(3, 10).Select
Selection.Copy
Range("OtherRange").Select
ActiveSheet.Paste

End Sub

Thanks

Resize Range - Macro
 

Unless you have a specific reason for activating a the first sell and
selecting the second (which are actually different operations), i
wouldnt bother. The following accomplished what you want to do in one
line:

Range("myrange", Range("myrange").End(xlDown)).Copy Range("otherrange")

Regards, Sam

Resize Range - Macro
 

Hi Sam,

Thank you for your response. I always need the macro to:

Sub GotToRangeResizeCopyPaste()

Application.Goto Reference:="MyRange" 'Go to a SELECTED range (no
name/unknown)
ActiveCell.Resize(3, 10).Select 'Resize it
Selection.Copy
Range("OtherRange").Select '(3) Go to a SELECTED range (no name/unknown)
ActiveSheet.Paste

End Sub

Please help. Thank you.


"Sam" wrote:

Unless you have a specific reason for activating a the first sell and
selecting the second (which are actually different operations), i
wouldnt bother. The following accomplished what you want to do in one
line:

Range("myrange", Range("myrange").End(xlDown)).Copy Range("otherrange")

Regards, Sam

Resize Range - Macro
 

so if i understand, you want to look at the current selection, and copy
an area 10 columns across and n rows down (n= number of used rows under
the selection) and past in a range called 'otherrange'?:

Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
10)).Copy Range("otherrange")

Resize Range - Macro
 

Hi Sam,

Thanks again for your response.

Actually, what I'm looking for is how to write a macro for the variable
(xlDown)of "3" (rows) on the macro below

ActiveCell.Resize(3, 10).Select

I need this macro for a lot of things because I name a range to go to, use
the REVISED macro above, then, I can use it for a lot of things, like copy,
put borders, insert rows, etc. I have been trying to get the right macro for
this.

Thanks again.



"Sam" wrote:

so if i understand, you want to look at the current selection, and copy
an area 10 columns across and n rows down (n= number of used rows under
the selection) and past in a range called 'otherrange'?:

Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
10)).Copy Range("otherrange")

Resize Range - Macro
 

I'm confused as the others, but maybe....

Dim myRng as range
with activesheet
set myrng = .range("myRange")
'this doesn't make much sense to me
'but it shows how to find the number of rows/columns in myrng
myrng.resize(myrng.rows.count,myrng.columns.count) .select
'or
'make it 10 columns wide
myrng.resize(myrng.rows.count,10).select
'which would be equivalent to:
myrng.resize(,10).select
end with

(if you specify the rows/columns to resize, then it won't be changed.)

Closer???


????? wrote:

Hi Sam,

Thanks again for your response.

Actually, what I'm looking for is how to write a macro for the variable
(xlDown)of "3" (rows) on the macro below

ActiveCell.Resize(3, 10).Select

I need this macro for a lot of things because I name a range to go to, use
the REVISED macro above, then, I can use it for a lot of things, like copy,
put borders, insert rows, etc. I have been trying to get the right macro for
this.

Thanks again.

"Sam" wrote:

? so if i understand, you want to look at the current selection, and copy
? an area 10 columns across and n rows down (n= number of used rows under
? the selection) and past in a range called 'otherrange'?:
?
? Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
? 10)).Copy Range("otherrange")
?
?

--

Dave Peterson

Resize Range - Macro
 

I'm sorry to confuse everybody. What I'm looking for is:

run a macro to find a name(cell), then
on this active cell, the macro would resize/highlight the rows and columns
below, then
I can add another macro to either copy, format the highligted rows, columns,
insert a rows, etc.

So, how can one write a macro to make the number of rows "3" to be variable.

ActiveCell.Resize(3, 10).Select

I am looking for a macro like this but the way end.xlDown is written below
won't work!
ActiveCell.Resize(end.xlDown, 10).Select

Thanks a lot




"Dave Peterson" wrote:

I'm confused as the others, but maybe....

Dim myRng as range
with activesheet
set myrng = .range("myRange")
'this doesn't make much sense to me
'but it shows how to find the number of rows/columns in myrng
myrng.resize(myrng.rows.count,myrng.columns.count) .select
'or
'make it 10 columns wide
myrng.resize(myrng.rows.count,10).select
'which would be equivalent to:
myrng.resize(,10).select
end with

(if you specify the rows/columns to resize, then it won't be changed.)

Closer???


????? wrote:

Hi Sam,

Thanks again for your response.

Actually, what I'm looking for is how to write a macro for the variable
(xlDown)of "3" (rows) on the macro below

ActiveCell.Resize(3, 10).Select

I need this macro for a lot of things because I name a range to go to, use
the REVISED macro above, then, I can use it for a lot of things, like copy,
put borders, insert rows, etc. I have been trying to get the right macro for
this.

Thanks again.

"Sam" wrote:

? so if i understand, you want to look at the current selection, and copy
? an area 10 columns across and n rows down (n= number of used rows under
? the selection) and past in a range called 'otherrange'?:
?
? Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
? 10)).Copy Range("otherrange")
?
?

--

Dave Peterson

Resize Range - Macro
 

You want to resize it based on the xldown???

dim myRng as range
dim myRows as long

set myrng = range("myRange")
myrows = myrng.end(xldown).row - myrng.row + 1

myrng.resize(myrows,10).select

maybe??????????



Danny wrote:

I'm sorry to confuse everybody. What I'm looking for is:

run a macro to find a name(cell), then
on this active cell, the macro would resize/highlight the rows and columns
below, then
I can add another macro to either copy, format the highligted rows, columns,
insert a rows, etc.

So, how can one write a macro to make the number of rows "3" to be variable.

ActiveCell.Resize(3, 10).Select

I am looking for a macro like this but the way end.xlDown is written below
won't work!
ActiveCell.Resize(end.xlDown, 10).Select

Thanks a lot

"Dave Peterson" wrote:

I'm confused as the others, but maybe....

Dim myRng as range
with activesheet
set myrng = .range("myRange")
'this doesn't make much sense to me
'but it shows how to find the number of rows/columns in myrng
myrng.resize(myrng.rows.count,myrng.columns.count) .select
'or
'make it 10 columns wide
myrng.resize(myrng.rows.count,10).select
'which would be equivalent to:
myrng.resize(,10).select
end with

(if you specify the rows/columns to resize, then it won't be changed.)

Closer???


????? wrote:

Hi Sam,

Thanks again for your response.

Actually, what I'm looking for is how to write a macro for the variable
(xlDown)of "3" (rows) on the macro below

ActiveCell.Resize(3, 10).Select

I need this macro for a lot of things because I name a range to go to, use
the REVISED macro above, then, I can use it for a lot of things, like copy,
put borders, insert rows, etc. I have been trying to get the right macro for
this.

Thanks again.

"Sam" wrote:

? so if i understand, you want to look at the current selection, and copy
? an area 10 columns across and n rows down (n= number of used rows under
? the selection) and past in a range called 'otherrange'?:
?
? Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
? 10)).Copy Range("otherrange")
?
?

--

Dave Peterson


--

Dave Peterson

Resize Range - Macro
 

Hi Dave,

Thanks for your reply. The macro below is what I am looking for. Its from
the excel help menu. I was able to add/exclude rows above and bellow and
add/exclude columns to the RIGHT by changing the numbers by TRIAL & ERROR.
However, I can't figure out how to EXCLUDE columns on the LEFT. Please show
me how. Thanks. Have a great weekend!

This example assumes that you have a table on Sheet1 that has a header row.
The example selects the table, without selecting the header row. The active
cell must be somewhere in the table before you run the example.

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select




"Dave Peterson" wrote:

You want to resize it based on the xldown???

dim myRng as range
dim myRows as long

set myrng = range("myRange")
myrows = myrng.end(xldown).row - myrng.row + 1

myrng.resize(myrows,10).select

maybe??????????



Danny wrote:

I'm sorry to confuse everybody. What I'm looking for is:

run a macro to find a name(cell), then
on this active cell, the macro would resize/highlight the rows and columns
below, then
I can add another macro to either copy, format the highligted rows, columns,
insert a rows, etc.

So, how can one write a macro to make the number of rows "3" to be variable.

ActiveCell.Resize(3, 10).Select

I am looking for a macro like this but the way end.xlDown is written below
won't work!
ActiveCell.Resize(end.xlDown, 10).Select

Thanks a lot

"Dave Peterson" wrote:

I'm confused as the others, but maybe....

Dim myRng as range
with activesheet
set myrng = .range("myRange")
'this doesn't make much sense to me
'but it shows how to find the number of rows/columns in myrng
myrng.resize(myrng.rows.count,myrng.columns.count) .select
'or
'make it 10 columns wide
myrng.resize(myrng.rows.count,10).select
'which would be equivalent to:
myrng.resize(,10).select
end with

(if you specify the rows/columns to resize, then it won't be changed.)

Closer???


????? wrote:

Hi Sam,

Thanks again for your response.

Actually, what I'm looking for is how to write a macro for the variable
(xlDown)of "3" (rows) on the macro below

ActiveCell.Resize(3, 10).Select

I need this macro for a lot of things because I name a range to go to, use
the REVISED macro above, then, I can use it for a lot of things, like copy,
put borders, insert rows, etc. I have been trying to get the right macro for
this.

Thanks again.

"Sam" wrote:

? so if i understand, you want to look at the current selection, and copy
? an area 10 columns across and n rows down (n= number of used rows under
? the selection) and past in a range called 'otherrange'?:
?
? Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
? 10)).Copy Range("otherrange")
?
?

--

Dave Peterson


--

Dave Peterson

Resize Range - Macro
 

Say your data in in A1:D9.

Then you can use the offset to "move" the range right or left:

Set tbl = Range("a1").CurrentRegion
tbl.Offset(1, 3).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count - 3).Select

This would select D2:D9 (moved over 3 columns and resized the number of columns
to avoid those first 3 columns).

When you're playing with this kind of stuff, you can set up a very small test
subroutine to do this--then swap back to excel to see if it selected what you
wanted.

ps. in .offset(x,y), both x and y can be positive, negative, or 0.



Danny wrote:

Hi Dave,

Thanks for your reply. The macro below is what I am looking for. Its from
the excel help menu. I was able to add/exclude rows above and bellow and
add/exclude columns to the RIGHT by changing the numbers by TRIAL & ERROR.
However, I can't figure out how to EXCLUDE columns on the LEFT. Please show
me how. Thanks. Have a great weekend!

This example assumes that you have a table on Sheet1 that has a header row.
The example selects the table, without selecting the header row. The active
cell must be somewhere in the table before you run the example.

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select

"Dave Peterson" wrote:

You want to resize it based on the xldown???

dim myRng as range
dim myRows as long

set myrng = range("myRange")
myrows = myrng.end(xldown).row - myrng.row + 1

myrng.resize(myrows,10).select

maybe??????????



Danny wrote:

I'm sorry to confuse everybody. What I'm looking for is:

run a macro to find a name(cell), then
on this active cell, the macro would resize/highlight the rows and columns
below, then
I can add another macro to either copy, format the highligted rows, columns,
insert a rows, etc.

So, how can one write a macro to make the number of rows "3" to be variable.

ActiveCell.Resize(3, 10).Select

I am looking for a macro like this but the way end.xlDown is written below
won't work!
ActiveCell.Resize(end.xlDown, 10).Select

Thanks a lot

"Dave Peterson" wrote:

I'm confused as the others, but maybe....

Dim myRng as range
with activesheet
set myrng = .range("myRange")
'this doesn't make much sense to me
'but it shows how to find the number of rows/columns in myrng
myrng.resize(myrng.rows.count,myrng.columns.count) .select
'or
'make it 10 columns wide
myrng.resize(myrng.rows.count,10).select
'which would be equivalent to:
myrng.resize(,10).select
end with

(if you specify the rows/columns to resize, then it won't be changed.)

Closer???


????? wrote:

Hi Sam,

Thanks again for your response.

Actually, what I'm looking for is how to write a macro for the variable
(xlDown)of "3" (rows) on the macro below

ActiveCell.Resize(3, 10).Select

I need this macro for a lot of things because I name a range to go to, use
the REVISED macro above, then, I can use it for a lot of things, like copy,
put borders, insert rows, etc. I have been trying to get the right macro for
this.

Thanks again.

"Sam" wrote:

? so if i understand, you want to look at the current selection, and copy
? an area 10 columns across and n rows down (n= number of used rows under
? the selection) and past in a range called 'otherrange'?:
?
? Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
? 10)).Copy Range("otherrange")
?
?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Resize Range - Macro
 

It worked perfectly. Thank you for the notes too! I don't have to do Trial &
Error.

Have a great weekend.

"Dave Peterson" wrote:

Say your data in in A1:D9.

Then you can use the offset to "move" the range right or left:

Set tbl = Range("a1").CurrentRegion
tbl.Offset(1, 3).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count - 3).Select

This would select D2:D9 (moved over 3 columns and resized the number of columns
to avoid those first 3 columns).

When you're playing with this kind of stuff, you can set up a very small test
subroutine to do this--then swap back to excel to see if it selected what you
wanted.

ps. in .offset(x,y), both x and y can be positive, negative, or 0.



Danny wrote:

Hi Dave,

Thanks for your reply. The macro below is what I am looking for. Its from
the excel help menu. I was able to add/exclude rows above and bellow and
add/exclude columns to the RIGHT by changing the numbers by TRIAL & ERROR.
However, I can't figure out how to EXCLUDE columns on the LEFT. Please show
me how. Thanks. Have a great weekend!

This example assumes that you have a table on Sheet1 that has a header row.
The example selects the table, without selecting the header row. The active
cell must be somewhere in the table before you run the example.

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select

"Dave Peterson" wrote:

You want to resize it based on the xldown???

dim myRng as range
dim myRows as long

set myrng = range("myRange")
myrows = myrng.end(xldown).row - myrng.row + 1

myrng.resize(myrows,10).select

maybe??????????



Danny wrote:

I'm sorry to confuse everybody. What I'm looking for is:

run a macro to find a name(cell), then
on this active cell, the macro would resize/highlight the rows and columns
below, then
I can add another macro to either copy, format the highligted rows, columns,
insert a rows, etc.

So, how can one write a macro to make the number of rows "3" to be variable.

ActiveCell.Resize(3, 10).Select

I am looking for a macro like this but the way end.xlDown is written below
won't work!
ActiveCell.Resize(end.xlDown, 10).Select

Thanks a lot

"Dave Peterson" wrote:

I'm confused as the others, but maybe....

Dim myRng as range
with activesheet
set myrng = .range("myRange")
'this doesn't make much sense to me
'but it shows how to find the number of rows/columns in myrng
myrng.resize(myrng.rows.count,myrng.columns.count) .select
'or
'make it 10 columns wide
myrng.resize(myrng.rows.count,10).select
'which would be equivalent to:
myrng.resize(,10).select
end with

(if you specify the rows/columns to resize, then it won't be changed.)

Closer???


????? wrote:

Hi Sam,

Thanks again for your response.

Actually, what I'm looking for is how to write a macro for the variable
(xlDown)of "3" (rows) on the macro below

ActiveCell.Resize(3, 10).Select

I need this macro for a lot of things because I name a range to go to, use
the REVISED macro above, then, I can use it for a lot of things, like copy,
put borders, insert rows, etc. I have been trying to get the right macro for
this.

Thanks again.

"Sam" wrote:

? so if i understand, you want to look at the current selection, and copy
? an area 10 columns across and n rows down (n= number of used rows under
? the selection) and past in a range called 'otherrange'?:
?
? Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
? 10)).Copy Range("otherrange")
?
?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Resize Range - Macro
 

Glad you got it working.

Danny wrote:

It worked perfectly. Thank you for the notes too! I don't have to do Trial &
Error.

Have a great weekend.

"Dave Peterson" wrote:

Say your data in in A1:D9.

Then you can use the offset to "move" the range right or left:

Set tbl = Range("a1").CurrentRegion
tbl.Offset(1, 3).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count - 3).Select

This would select D2:D9 (moved over 3 columns and resized the number of columns
to avoid those first 3 columns).

When you're playing with this kind of stuff, you can set up a very small test
subroutine to do this--then swap back to excel to see if it selected what you
wanted.

ps. in .offset(x,y), both x and y can be positive, negative, or 0.



Danny wrote:

Hi Dave,

Thanks for your reply. The macro below is what I am looking for. Its from
the excel help menu. I was able to add/exclude rows above and bellow and
add/exclude columns to the RIGHT by changing the numbers by TRIAL & ERROR.
However, I can't figure out how to EXCLUDE columns on the LEFT. Please show
me how. Thanks. Have a great weekend!

This example assumes that you have a table on Sheet1 that has a header row.
The example selects the table, without selecting the header row. The active
cell must be somewhere in the table before you run the example.

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select

"Dave Peterson" wrote:

You want to resize it based on the xldown???

dim myRng as range
dim myRows as long

set myrng = range("myRange")
myrows = myrng.end(xldown).row - myrng.row + 1

myrng.resize(myrows,10).select

maybe??????????



Danny wrote:

I'm sorry to confuse everybody. What I'm looking for is:

run a macro to find a name(cell), then
on this active cell, the macro would resize/highlight the rows and columns
below, then
I can add another macro to either copy, format the highligted rows, columns,
insert a rows, etc.

So, how can one write a macro to make the number of rows "3" to be variable.

ActiveCell.Resize(3, 10).Select

I am looking for a macro like this but the way end.xlDown is written below
won't work!
ActiveCell.Resize(end.xlDown, 10).Select

Thanks a lot

"Dave Peterson" wrote:

I'm confused as the others, but maybe....

Dim myRng as range
with activesheet
set myrng = .range("myRange")
'this doesn't make much sense to me
'but it shows how to find the number of rows/columns in myrng
myrng.resize(myrng.rows.count,myrng.columns.count) .select
'or
'make it 10 columns wide
myrng.resize(myrng.rows.count,10).select
'which would be equivalent to:
myrng.resize(,10).select
end with

(if you specify the rows/columns to resize, then it won't be changed.)

Closer???


????? wrote:

Hi Sam,

Thanks again for your response.

Actually, what I'm looking for is how to write a macro for the variable
(xlDown)of "3" (rows) on the macro below

ActiveCell.Resize(3, 10).Select

I need this macro for a lot of things because I name a range to go to, use
the REVISED macro above, then, I can use it for a lot of things, like copy,
put borders, insert rows, etc. I have been trying to get the right macro for
this.

Thanks again.

"Sam" wrote:

? so if i understand, you want to look at the current selection, and copy
? an area 10 columns across and n rows down (n= number of used rows under
? the selection) and past in a range called 'otherrange'?:
?
? Range(ActiveCell.Range("a1"), ActiveCell.End(xlDown).Offset(0,
? 10)).Copy Range("otherrange")
?
?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson