PDA

View Full Version : Quick Question About XlDown



mbrill
09-04-2018, 05:55 AM
Hi,

One quick question, I am writing VBA code to make a dynamic array. In order to get the number of row I am using

row4 = Range("O8", Range("I7").End(xlDown)).Cells.Count - 1

This code, the question is: Is it true that it will also count the blank cell that blanked because of the formula?

I mean, in the cell I have the formula of Iferror that the error value will be blank. I wonder if there is a way to overcome this issue.

Thanks in Advance.

Kenneth Hobs
09-04-2018, 05:59 PM
Welcome to the forum!

Do it manually to see.

Of course cells.count gives you cells in the Range(), not rows.

Normally, one sets the first cell as top left of block and 2nd cell in Range() as the bottom right. You may want to see how to handle the case where a blank is in either of the two column rows or both or neither.

I usually go by a required column when setting a range block. I also use cells(rows.count, "A").end(xlup) when I want to get last cell from bottom up with a value.

mbrill
09-06-2018, 07:53 PM
Thanks Mr. Kenneth for the reply, I am aware that I can use the end(xlup) to avoid the count stop if there are blank cells in the middle, but in my case, they shouldnt be any blank cell. And currently I think the xldown will count cell that blanked because of formula (e.g. if I am using iferror, with the error value of ""). Thus do you have any recommendation to avoid that issue? because it counted more that it should be.

Thanks.

Kenneth Hobs
09-06-2018, 08:42 PM
IF you did it manually as I first replied, you will see that it functions as designed. If you don't like that, you need an alternative method. I suggested just one. Others could be a Do loop.