How to have a dynamic Range in VBA in Excel?

[Rev12]

Hi everyone!

I have the code below to run for Paste Special (xlPasteFormulas, xlPasteValues)

Please Login or Register  to view this content.

------My concern is how to make the . Range("AC6:AC11880") & .Range("AB5:AB11880") dynamic,
so that when I am going to add "4 or more Data" the Range should also be increasing to something like . Range("AC6:AC11884") & .Range("AB5:AB11884)

Any help is really appreciated.

Thanks in advance,

Rev12

[MarvinP]

Hi Rev12,

There are two possible answers to your question. First is by using Dynamic Named Ranges in the Formulas Named Ranges tool. Then use the named range in your VBA instead of the range letters.

See https://www.vertex42.com/blog/excel-...ed-ranges.html and to use the named range in your vba see:
https://excel.tips.net/T003106_Using...n_a_Macro.html

Second would be to find the last entry in one of your longest column using a VBA variable. I like to create a variable called LastRow so I can remember what it is. Then:

Please Login or Register  to view this content.

Then instead of using your

Please Login or Register  to view this content.

I'd make it..

Please Login or Register  to view this content.

Read: https://www.thespreadsheetguru.com/b...lumn-using-vba

I hope this helps.

[jolivanes]

Or for individual lengths of Columns:

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

More typing though!