variables for ranges and their use in functions.

**NicholasL** · 06-12-2013, 02:56 PM

Okay. So I've been having major trouble with using variables in functions. I can never get it to work and so far there is no distinguishable pattern between how I get it to work in one case and how I get it to work in another -- except "&" seem to be involved a bit.

Regarldess, here is a snippet of code:

Please Login or Register  to view this content.

If it is not clear: what I'm trying to do is start at the beginning of a row, go until the end of that row, and copy everything in between (including the end points). Except I don't know how long the row is going to be so I need to use a variable. Thank you for your time, I appreciate it.

**Mordred** · 06-12-2013, 03:40 PM

Hi NicholasL,

Hopefully the following will give you some insight

Please Login or Register  to view this content.

**AB33** · 06-12-2013, 03:44 PM

Nic,
I am not surprised you are confused. When I start learning VBA, Do/While loop used to confuse me a lot. You can usually get in to endless loop. I always tried to avoid using these loops. If you are looping from one-end to another use ,For next loop. After you reached the last loop the code stops itself.

This is my guess what are trying to do. You want to loop from C1 all the way down until the last non-empty row in column C and copy.

Please Login or Register  to view this content.

**Mordred** · 06-12-2013, 03:44 PM

To learn better, type my code in exactly as you see it, without the comments or with that's up to you. As you type you'll see the VBE prompt you with different actions and whatnot that you can perform on the worksheet ws1 or the range rRng or each oC in rRng. This will help you learn how to go about coding and manipulating things. The code I gave you will use the range between B1 and the last column used but there are other ways to accomplish the same thing. I encourage you to start with what I gave you though and learn from there.

PS, ask as many questions as needed, no question is a stupid question in my opinion.

**Mordred** · 06-12-2013, 04:02 PM

Another thing, before you stop looking at this: if you attempt to write code the way I have suggested, you'll learn that you no longer need to make sheet selections or cell selections. For instance, if you have

Please Login or Register  to view this content.

or have those ranges filled dynamically, you won't need to copy/paste if your requirement is to have sheet2 range the same (pasted) as sheet 1 range:[code]rng2.Cells.Value = rng1.Cells.Value[code]Hopefully this isn't confusing you too much. I just wanted to show you that selections are no longer needed to move values from one range to another.