How can I dynamically change the range for Range("A1:M500")?
 

I initially clear the following Range("A1:M500") in one of my worksheets.
Using VB, I add data to this worksheet so only 10-20 rows have data. How do
I change my range dynamically based on the number of rows with data and
columns with data?

Reason I want to do this is because I want to set my print area in VB.

How can I dynamically change the range for Range("A1:M500")?
 

ActiveSheet.PageSetup.PrintArea = Range("A1").CurrentRegion

"Bij" wrote:

I initially clear the following Range("A1:M500") in one of my worksheets.
Using VB, I add data to this worksheet so only 10-20 rows have data. How do
I change my range dynamically based on the number of rows with data and
columns with data?

Reason I want to do this is because I want to set my print area in VB.

How can I dynamically change the range for Range("A1:M500")?
 

Sorry, NOT working. Have to try harder.

"PY & Associates" wrote:

ActiveSheet.PageSetup.PrintArea = Range("A1").CurrentRegion

"Bij" wrote:

I initially clear the following Range("A1:M500") in one of my worksheets.
Using VB, I add data to this worksheet so only 10-20 rows have data. How do
I change my range dynamically based on the number of rows with data and
columns with data?

Reason I want to do this is because I want to set my print area in VB.

How can I dynamically change the range for Range("A1:M500")?
 

You nearly had it:

ActiveSheet.PageSetup.PrintArea = Range("A1").CurrentRegion.Address

Regards
Rowan

"PY & Associates" wrote:

Sorry, NOT working. Have to try harder.

"PY & Associates" wrote:

ActiveSheet.PageSetup.PrintArea = Range("A1").CurrentRegion

"Bij" wrote:

I initially clear the following Range("A1:M500") in one of my worksheets.
Using VB, I add data to this worksheet so only 10-20 rows have data. How do
I change my range dynamically based on the number of rows with data and
columns with data?

Reason I want to do this is because I want to set my print area in VB.