Trying to find the next empty cell in a range Using With and Set method - Help

[Poundland]

Guys,

I am trying to find the next empty cell in a range, and to assign the row number to a variable based on the result, the answer that I am coming up with does not make sense. The code returns a row reference that is not empty. Do you know why this is not working...


With Worksheets(1).Range("a4:a1000")
Set c = .Find("", LookIn:=xlValues)
RW = c.Row
End With

[Ago]

It works for me.
What is the problem? Do you have a sample workbook?

[mancubus]

hi. try this for Column A. LastRow + 1 is the first blank cell's row number (bottom-up)
LastRow = Worksheets(1).Columns(1).Find("*", , , , xlByRows, xlPrevious).Row

[mancubus]

or
FirstBlankRow = Worksheets(1).Columns(1).Find("*", , , , xlByRows, xlPrevious).Offset(1).Row

[snb]

msgbox sheets(1).cells(rows.count,1).end(xlup).offset(1).row

[Poundland]

Neither of the suggestions have worked, they are still referencing a cell that has data in;

I have coded this which now works fine, I have no idea what the original code did not, work it should have done...


Dim ws As Worksheet
Dim RW As Long
Set ws = ActiveSheet
RW = ws.Range("a4").End(xlDown).Row + 1

[mancubus]

i've been using the methods in posts 3, 4 and 5 for years with success.

providing that column a does not contain a blank cell between rows 4 and RW methods in post #5 and #6 will produce the same result.

#5: you're selecting A1048576 and then pressing ctrl+UpArrow.

#6: you're selecting A4 and pressing ctrl+DownArrow.

if you do these manually, you'll see you're selecting the same cells.

method in #3 and #4 is like hitting ctrl+F, typing * in Find what box. but searching from bottom to up (xlPrevious) .


PS: what if there is a blank cell (or a cell is accidentally cleared)? xlDown will fail. instead, i recommend you use xlUp as in post#5.

[Poundland]

I have changed my coding based on the previous post, so that it now starts looking at the last cell in the column, and works up, rather than down.. the result is the same, but will as stated eliminate the chance of finding deleted cells or cleared cells between the starting cell and the end cell...

Thank you all for your help..

P.S. I have no idea why my original code, and the given code lines on this post did not work, when I manually select the starting cell and CTRL+Down Cursor I get to the empty cell that is found in the last code posted, yet the other code lines, plus my original reference a cell in the middle that has data in it... it's a mystery...