VBA code to store the count of last row with value

Hi everyone… so I attempted to create a macro that could automatically VLOOKUP a dozen of columns. I managed to create it just that now I’m having a little problem regarding the parameter of the lookup. So I set it from A2:Z100 now. This way this macro would lookup up to row 100 no matter how many data I inputted (for example, I only input 2 rows of data, but the macro would VLOOKUP until 100 rows with row 3-100 have #N/A)

It’s not aesthetically pretty so I wanted to make a variable called LastRow that could store the number of the last row filled with a value so that the parameter would be “A2:Z & LastRow” instead of “A2:Z100”. I did try to make it using Row.Count but somehow it ended up looking up until 10000+ rows and that’s even worse…

Can anyone help me with this? Any help would be appreciated!

So far I only have this:

Dim LastRow as Integer
LastRow = ….

Thank you so much in advance! Hope you all have a good day!

Hi,

as a reminder LastRow should not be Integer according of a worksheet total rows and
such variable is useless, just using a With block on the CurrentRegion for example …

Hi everyone!
Thank you for your responses!

maxpit, I've tried the code but it doesn't work... somehow it affects my lookup code too. Is there another way?

Marc L, oh, then what should it be? Is it Long? And what is With block? I'm sorry, my knowledge is limited about this...

Thank you all! Hope to hear from you soon!

Maxpits code is correct. Did you adjust the column value inside the Cell() function before using it? And yes, Long would be a better choice.

Originally Posted by chuluoyi

Is it Long?

Yes ! In case of big data …