Solved: Find empty cell

[cavemonkey]

Hi

I have a little problem on trying to find the correct empty cell for my spreadsheet, especially when a merged cell is involved. I need to find the correct empty cell in order to make my rest of the code work properly.

Below is the attached excel file which should better explain what I'm trying to do.

Thanks

[mikerickson]

Does this do what you want?
Dim foundRay As Range

Set foundRay = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Do Until foundRay.MergeArea.Address = foundRay.Address
Set foundRay = foundRay.Offset(1, 0)
Loop

[geekgirlau]

especially when a merged cell is involved

... and there's your problem. Merged cells cause more trouble than they're worth - try to use Center across Selection instead.

[unmarkedhelicopter]

Oh in about 10,000, or so, spreadsheets that I have seen, I have only ever seen maybe 2 that needed to use merged cells.

They are the spawn of beelsebub !!!

[cavemonkey]

hmm I tried your code. But then again since my original purpose is to find the empty cell in the sheet. What if there are actually cells that contain things.

I thought of just using if-then statement to find but these 2 situations.



Sub test1()
Sheets("Sheet4").Select
If Cells(8, 1) = "(if this is a blank cell)" Then
empty_cell = ActiveCell
ElseIf Cells(8, 1) = "(I don't really know how to write for this part)" Then
rowcount = 8
Do Until Cells(rowcount, 1) = ""
rowcount = rowcount + 1
Loop
empty_cell = rowcount
End If
End Sub



Green are my own comment
See attached. Should give a clearer view.