0

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.

divy.h
  • 23
  • 1
  • 6

2 Answers2

3

"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)
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
VBA Pete
  • 2,656
  • 2
  • 24
  • 39
  • Shouldn't `rng` be taken off the `wc` sheet? – Mathieu Guindon Jan 13 '17 at 19:06
  • @Mat'sMug: probably, but I am not sure what the full code does since only a part of it was shared. Thanks for the edit by the way! – VBA Pete Jan 13 '17 at 19:09
  • Thank you. But my editing was not up to mark. Because poor editing leads you to "wc" doubt. Here wc stand for woorksheet . BTW your code will include only a range from first cell (C4) to last cell (C430), which is i am assuming.@Mat's Mug – divy.h Jan 13 '17 at 19:24
  • @divy.h No worries, btw Mat's Mug was suggesting to change Set rng = ActiveSheet.Range("C4:C" & erow) to Set rng = wc.Range("C4:C" & erow). And if erow = 430 then the range will be all the way to C430. If you want, you can check this using msgbox(erow) in your code. – VBA Pete Jan 13 '17 at 19:30
-2

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.

mkinson
  • 172
  • 4
  • 16
  • This doesn't answer the OP's question, and suggests an unreliable way of getting the last row. Try typing "Hi" in Z274 in a workbook that has a table going from A1 to C42, then delete the contents of Z274. The `UsedRange` will be A1:Z274, which is wrong. – Mathieu Guindon Jan 13 '17 at 19:05
  • But the "variable" component in OP refers to the variable `erow` in the context of "my range will change as user keeps on adding new data". – David Zemens Jan 13 '17 at 19:14
  • @Mat'sMug If you delete the contents of a cell it no longer is counted as part of the UsedRange. I just tested with data in Columns 1-6 and rows 1-100 then added info into Z274, UsedRange has from A1:Z274. Deleted the contents of Z274 and UsedRange is updated to A1:F100. – mkinson Jan 13 '17 at 19:57
  • Yeah.. he highlighted the cell. It's therefore used. – mkinson Jan 13 '17 at 20:00