Finding the first empty cell in a column using VBA without a loop [duplicate]

Question

So I have these two lines of code in VBA that find an empty cell at the bottom of a column and then return a pointer the next cell.

RowPointer = ThisWorkbook.Sheets("Current").Cells(65000, 4).End(xlUp).Row
If ThisWorkbook.Sheets("Current").Cells(RowPointer, 4).Formula <> "" Then RowPointer = RowPointer + 1

The problem is this searches from the bottom up, and I need to find the first empty cell in a column not the last. I've seen a few ways to fix this that use a loop to go through the column but the spreadsheet I'm working with is rather large and a loop would take a long time to execute. So my question is is there an easy way to search through a column from the top down to find the first empty cell?

@brettdj is it a duplicate? If A1:A10and A12-A20 are non-empty then first blank = A11, last non-empty = A20, no? — Ioannis
– Ioannis, Commented Jun 23, 2014 at 13:38
@Ioannis yes, because it is just a small change on using the Find (although admittedly I did link back to a non-blank in a row rather than a blank in a column question) .... — brettdj
– brettdj, Commented Jun 23, 2014 at 13:40
Adapting the code in the question I linked to to Set ws = Sheets("Current") then Set rng1 = ws.Columns(4).Find("", ws.Cells(Rows.Count, "D"), xlFormulas, , xlByColumns, xlNext) would find the first blank result — brettdj
– brettdj, Commented Jun 23, 2014 at 13:47

Ioannis · Accepted Answer · 2014-06-23 13:18:50Z

0

Maybe something like

RowPointer = Sheets("Current ").Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1,1).Row

Posting from mobile so I hope it works!

answered Jun 23, 2014 at 13:18

Ioannis

5,4682 gold badges21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

brettdj Over a year ago

Would work but (1) Special Cells should always be used in conjunction with error checking (2) wont catch a formula blank (though I note the question does say empty cell

Ioannis Over a year ago

Thanks for the feedback! My thinking for (1) was that in this particular case it will return an error when the whole column is filled (this does refute the need for error-traping but in practice it should be robust) and for (2) I noticed as well that the cell should be empty rather than empty-looking.

Leo Chapiro · Accepted Answer · 2014-06-23 12:54:23Z

0

Try this (I have not tried yet):

RowPointer = ThisWorkbook.Sheets("Current").Cells(0, 4).End(xlDown ).Row
If ThisWorkbook.Sheets("Current").Cells(RowPointer, 4).Formula <> "" Then RowPointer = RowPointer + 1

answered Jun 23, 2014 at 12:54

Leo Chapiro

14k8 gold badges67 silver badges95 bronze badges

Collectives™ on Stack Overflow

Finding the first empty cell in a column using VBA without a loop [duplicate]

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related