Using Number of Rows in a Column to Make a Range for a Different Column

Hi all,

I am very new to VBA after spending the vast majority of my time in Excel writing formula's instead. (And I'm by no means amazing at that)
However something I can do via formula, seems to be very hard for me to achieve in VBA.

Basically I would like a macro that finds the last cell of a column, in my attached example workbook that's Row 26 in Column D, then uses that number 26 to make a new range. Eg E2:E26. (Where D, E, 2 and 26 are all variable)
I have be advised by a friend of mine to make sure all variables are kept at the top of the code so they can easily be changed if needs be rather than trawling through code.
Equally they advised me to make sure I refer to the workbook and sheet, so the macro can be used in other places and with consequences while running. (Similarly I avoid select and activate where possible)

I have attached a sample workbook and included a basic example of what I'm after. (And one of my latest failings)

Finally it's worth mentioning that I have tried this with loop and although it works, it's not practical to use in my original file which has over 1000+ rows so it would be a lot of volatile random numbers.
I more hope to make a range based on the number of rows in a different column and then later refer to that new range to manipulate with copy etc.

Cheers in advance
EricDonk

Edit: I have tried to find an answer to this using all sorts, but everything I saw online doesn't actually do what I'm after.

Does this work for you?

Please Login or Register  to view this content.

Originally Posted by ByteMarks

Does this work for you?

Please Login or Register  to view this content.

Hi ByteMarks,

Cheers for your code it does as half as intended but unfortunately relies on the sheet being active which it isn't.
Is there a way to make this code work when run from another sheet/ workbook ?
As I side note I won't need to clear the column because I will run over it each time and there will never be just a header row. (Appreciate the added error handling though)

Thanks.
EricDonk

relies on the sheet being active which it isn't

How is the sheet/workbook identified?
Usually if something is the result of a button click which this appears to be, the button is on the sheet being processed and is therefore the active one.

As I side note I won't need to clear the column because I will run over it each time and there will never be just a header row.

If column D is shorter than it was before, part of column E won't be run over.

Originally Posted by Mumps1

Try:

Please Login or Register  to view this content.

This works really well, thank you so much for all the codes as well as your time Mumps1. This issue had really been getting on my nerves but you solved it so quickly. Cheers again.

One last question and I'm fine if the answer is "why would you need that" or "It's not worth it" but at the moment I can amend the values "E" and "D" and start at row "2" to suit my needs but out of curiosity is it easy to make them variable too? (like I was attempting in my code... Eg using Dim or something?

Thanks once more Mumps1, Rep added and marked as solved.
EricDonk

Originally Posted by ByteMarks

How is the sheet/workbook identified?
Usually if something is the result of a button click which this appears to be, the button is on the sheet being processed and is therefore the active one.

Cheers for getting back to me ByteMarks, I'm so new to VBA and am only going off what a friend said because they advised me tend to always mention the workbook and sheet, them put them as a variable so they can easily be changed in future.
Also while reading up on VBA I saw online that using select and active should be avoided where possible, but again am new to this that I trust your experience and reputation.

Originally Posted by ByteMarks

If column D is shorter than it was before, part of column E won't be run over.

This is a great observation and not one I'd thought of, however the list in the column D will never get shorter only stay the same or get longer.

Thanks again for your code and input. EricDonk