CODEWITHSUNDEEP
vbaexcel
divy.h
divy.h

Reputation: 23

can i make my range variable?

I want to create a program in which my range will change as user keeps on adding new data.

 dim rng as range  
dim erow as integer
erow = wc.Cells(wc.Rows.Count, 3).End(xlUp).Row + 1
set rng =activesheet.Range("c4:c24002")' you will find this thing useless. but this i only part of my program

I could use last cell reference to define my range. But this will add unnecessary process. So i though of trying "Range("C4:Cerow")". Unfortunately, vba do not allows such things. So if it is possible pls guide me.

Upvotes: 0

Views: 269

Answers (2)

mkinson
mkinson

Reputation: 172

In your active worksheet you can use the ActiveSheet.UsedRange method. This will extend your range to include the furthest column and row that contain information in them.

As an example


Dim rng as Range
Set rng = ActiveSheet.UsedRange

Transferring from Comments to reduce spam.

What using UsedRange allows you to do is reference that range with rng.Address to plug in the range limits into your code.

As an example: 

ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
will give you your last row and 
ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
will give you your last column.

It all depends on what the OP needs the code for. If they plan to have null values in column "C" but have data in other columns then you cannot simply do xlUp. Defining the used range and then using Offset I find to be the best method.

Upvotes: -2

VBA Pete
VBA Pete

Reputation: 2666

"C4:Cerow" is a string literal, VBA can't guess what you mean here; you need to concatenate the "C4:C" part with the string representation of the value of the erow variable.

Like in the following code:

Dim erow As Long
erow = wc.Cells(wc.Rows.Count, 3).End(xlUp).Row + 1

Dim rng As Range  
Set rng = ActiveSheet.Range("C4:C" & erow)

Upvotes: 3

Related Questions