Select a range from the active cell till the final row used

[Megatronixs]

Hi all,

I have a workbook that I need to open with a macro and copy a range from it to another workbook.
as the columns will not be always on the same possiton I had to create a find the text in the column and select it.
The next step was to go 1 cell below and activate it. Now I have problems creating the range from the active cell till the end of the last used cell in that column.

So far I have this:

Please Login or Register  to view this content.

Any help will be really great ;-)

Greetings.

[ragulduy]

Please Login or Register  to view this content.

will work on column A, presumably you want cells(rows.count,activecell.column)
so to select:

Please Login or Register  to view this content.

[Megatronixs]

Hi yudlugar,

It works really great. Thanks a lot for your great help.

Greetings.

[Megatronixs]

Hi yudlugar,

Can I ask you one more thing?

How is it possible to use the below macro to actually make the selection from 1 cell lower?
Now when the macro runs, it will select the range, but it will also select a cell that has a value. I should make the selection excluding the cell that has a value and make a range from the empty cells.

I have data in column I that the macro uses as example then it will go to column B and find fill up with data till one cell befor the one with data (in my case till column "B"row 13 has data and in column "I" till 26, so it should make a range from B26 till B14)

Please Login or Register  to view this content.

Greetings

[ragulduy]

You can use .offset(1,0) to get the cell that is one lower:

Please Login or Register  to view this content.

for example

[Megatronixs]

Hi,
I guess I did not express myself good, sorry for that.
Please check the attached excel file where you can see what the outcome should be.
I have put the fill color in green so you can see where the results should be (the previous macro will go up in column B to the used cell and then it will overwrite it, it actually should go up and make the range with only empty cells.

Greetings.

[Megatronixs]

Hi,
I found a solution, it is a little bit complicated, but works :-)

Maybe some one knows a simpler and faster way to do the same.

Please Login or Register  to view this content.

Greetings.

[ragulduy]

You don't need the select stuff:

Please Login or Register  to view this content.

[Megatronixs]

Hi,
Thanks :-)
I will try this out as soon as I can and let you know.

Greetings.

[Megatronixs]

Ok, it is working. Thanks a lot.