0

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?

Calvin Miller
  • 25
  • 3
  • @brettdj is it a duplicate? If `A1:A10`and `A12-A20` are non-empty then first blank = `A11`, last non-empty = `A20`, no? – Ioannis Jun 23 '14 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 Jun 23 '14 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 Jun 23 '14 at 13:47

2 Answers2

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
Leo Chapiro
  • 13,678
  • 8
  • 61
  • 92
0

Maybe something like

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

Posting from mobile so I hope it works!

Ioannis
  • 5,238
  • 2
  • 19
  • 31
  • 1
    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* – brettdj Jun 23 '14 at 13:42
  • 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. – Ioannis Jun 23 '14 at 14:11