Finding empty row with End(xlUp) sometimes gives the wrong row

[Ellana]

Hi.

I have been looking into this for a bit, but i simply cannot seem to make sense of this issue.

I am using the following piece of code, to find the next empty row. This is used to enter data from a userform.

emptyRow = Cells(Rows.Count, "C").End(xlUp).Offset(1).Row

Sometimes it will work properly, other times it will select row 5010. The spreadsheet has empty but formatted cells down to 4997, so it's really odd to me that it's 5010 specifically, and not 4998 as an example.

It's a fairly new sheet, so there's only data in row 3-19, plus the headers.

If i close the sheet and VBA editor (builtin), i works fine for a few entries, before it gives up again, and gives me row 5010.

Row 5010 is locked, so trying to enter data into it, will give me an error 1004.

Any ideas what

 

[Kerryx]

try this

emptyrow = Cells(Rows.Count, "C").End(xlUp).End(xlUp).offset(1).Row

 

[Ellana]

try this

emptyrow = Cells(Rows.Count, "C").End(xlUp).End(xlUp).offset(1).Row

That leads me to row 3, the first row with data in it. Ran it 5 times in a row, all with the same result.

 

[Kerryx]

ok added extra offset(1)

Sub emptyrow()
Dim emptyrow As Long
emptyrow = Cells(Rows.Count, "C").End(xlUp).Offset(1).End(xlUp).Offset(1).Row
MsgBox emptyrow
End Sub

 

[Ellana]

ok added extra offset(1)

Sub emptyrow()
Dim emptyrow As Long
emptyrow = Cells(Rows.Count, "C").End(xlUp).Offset(1).End(xlUp).Offset(1).Row
MsgBox emptyrow
End Sub

View attachment 70821

Thank you very much for your thoughts, efforts and attempts.

I'm afraid this might be caused by something else. I had an old spreadsheet open, to manually insert data from the old one to the new one. The old one was made with a timer function, to make sure it would shutdown if it had been idle for 10 minutes, so others could use it (offline storage doesn't allow co-authoring).

When i had that spreadsheet open, it would act up. Now i made a screenshot, and made 5 entries into the new sheet, without any kinds of problems. So i believe there was some kind of clash between the two sheets.

Sorry to waste your time, but it's immensely appreciated.

 

[Kerryx]

ok no problem glad you got it sorted