Variable Range

Hi
I am trying to write code to select a range of cells, copy and paste to sheet2
In my sheet i have a button that adds a line copying the format of the cells above
i need to be able to select all the text in column A down as far as the add Line button but not including the is cell
I have the code to select all as seen below. How can i select all the text from A3 to the cell just above the cell named "LastCell"

Please Login or Register  to view this content.

Hi Lynn81,

I think you need a line of code that I use very often.

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

The above line of code will return a number of the last row with data in it, in column A. Then I believe you want LastRow = LastRow - 1?

How about this one then

LastRow = Cells(3, "A").End(xlDown).Row

Which will Start in Cell A3 and do a Ctrl+Down to find the last cell before a blank in column A.

Hi Lynn,

I guess I need to see a sample worksheet to see why you are having this problem. You can attach one by going to the Advanced Message type and clicking on the Paper Clip Icon above the message area.

Sorry, I just don't understand what your need is.

Hi lynn81,

I'm not sure I understand the question still. I've passed this on to Arlu1201, our Head Guru for a look. They will get back to you shortly.

Lynn81,

Try this code -

Please Login or Register  to view this content.

Hi,

Now that Arlette has solved your question (I think?)

I think you need to use Range("LastCell").Row to get the row you want. Is that correct? Did Arlette's code help? I don't understand why s/he made the variables global. Arlette?

I think you need to use Range("LastCell").Row to get the row you want.

You do not need to use this. I've drafted the code upto the copy part.So the range required is already selected. Just specify the location where the data has to be pasted.

Global variables is out of habit. You can even move the dim statements below the sub copy_data() line.

I'missing something. From the example workbook

I Need to be able to select from A3 down to the cell above the button "ADD LINE" which can vary
My code at the moment for the copy and move picks up all the cells from A3 to
the cell under the button called "LastCell"

You cannot use a button to mark cells really.

I can't see where Arlu is getting "End" from, it's not in the sample data. Much more efficient code if we are looking for End is to automate AutoFilter. Also, for efficiency it's unnecessary to select the cells.

Attach a better example

If you move the button aside, you will find the word "End" which is also a named range "Lastcell".

Hi Arlette,

I really think my suggestion might help Lynn81 understand her code a little better. I'm a little hurt with the suggestion that she doesn't need my suggestion.

I'm studying your code and learning a lot from it. Thanks for showing how you'd do this problem.

I still wouldn't bother with Looping.

Please Login or Register  to view this content.

Simon,

Find can surely be used. There are multiple ways to get the same solution. I just suggested a code i was comfortable with. The user is free to use it or any other code provided by any other user.

Hey Simon,

I'm always ready to learn from reading other people's code. I learned a lot from reading Arlette's. S/he showed me how to create global variables, instead of locals, that I'm not sure are good or needed for this example. I also saw a jump out of a loop based on a condition. I'm not sure that is good either. Many times I simply miss what the OP is asking for. The question of a named range of "LastCell" was mentioned twice. I did see the "End" under the button (sorry Roy) but don't see how that addressed the named range question.

The bottom line is that the OP learns something and gets better at understanding Excel and its possibilities.
Thanks for the support.

I haven't use End, I used the named Range. Arlette's code looped until it found "End" - OK on small data sets but not efficient for large amounts of data.

I didn't notice the text hidden behind the button at first. The OP had a Named range so it's best to use that.

Thanks Roy n Simon. A learning for me too.

Originally Posted by MarvinP

Hey Simon,

I'm always ready to learn from reading other people's code. I learned a lot from reading Arlette's. S/he showed me how to create global variables, instead of locals, that I'm not sure are good or needed for this example. I also saw a jump out of a loop based on a condition. I'm not sure that is good either. Many times I simply miss what the OP is asking for. The question of a named range of "LastCell" was mentioned twice. I did see the "End" under the button (sorry Roy) but don't see how that addressed the named range question.

The bottom line is that the OP learns something and gets better at understanding Excel and its possibilities.
Thanks for the support.

Marvin, your lucky to have those skills passed on to you, you'll be creating spaghettti code in no time at all

Hey Simon,

I didn't say I was learning "good" things, just learning how others attack problems, give solutions, and reject other's suggestions. I've written enough spaghetti code to see it in action and shy away from it. It's always fun to see others who travel that path and get lost a few weeks later. It also allows me to see how accomplished programmers are by what they write.

Marvin, when i wrote that i felt a serious lump in my cheek