Using XLDown in a loop to find next blank in a column

[JimmyCobblers]

Hi

DILIPandey kindly helped me out the other day, writing the below code, which does what it needs to do in my new report. However I was wondering if it could modified slightly so that it skips to the next blank using XL down somehow, rather than evaluating every cell?

I've turned off the ScreenUpdating for my report and it's not very big anyway. I just wonder if I tried it with something bigger, the loop would take a while.


I tried modifying it at the ActiveCell.Offset(1, 0).Select part but it jumped down to the bottom, skipping blank cells in column B, so I assume it was refering to the I formula.

thanks

Please Login or Register  to view this content.

[JosephP]

perhaps

Please Login or Register  to view this content.

[Norie]

If you want to put a formula in each blank cell of a column perhaps you could use SpecialCells.

Please Login or Register  to view this content.

The formula might need adjusted for this to work.

[JimmyCobblers]

Hi JosephP I tried your code but it came up with 'Run Time error '1004' No cells were found. Needs a tweak somewhere.

Norie, I am about to leave work so I can't try your solution, but would that work for as long there is something in column A?

Please Login or Register  to view this content.

[Norie]

Jimmy

If Joseph's code doesn't work then mine won't either, for the same reason.

Are there actually blank cells in column B, not formulas that return "" or similar.

[JimmyCobblers]

Hi

No formulas etc in the cells, checked format set to general etc. thanks

[Norie]

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[JosephP]

well they're not blank as far as excel is concerned-perhaps you pasted them from somewhere? perhaps

Please Login or Register  to view this content.