[RESOLVED] Find next empty row, when data may/may not be in first column

[timh1234]

I am trying to write a code that pastes data entered into a Userform into the next empty row.

All is good, except for finding the empty row. Currently, I use the code:

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

But this only searches in column A for data. How do I find the empty row, when data could be entered into columns up to column Y (and beyond if possible)?

Thanks.

[opus]

Do it as you would do manually, that is: If you need to look in all that columns then just do it.
Your EmptyRow will be the Maximum rowNumber found in all that columns.

[timh1234]

Yeah I thought that would be a straight-forward solution (and it probably still is) but to do that, how would I manipulate the above code?

I have tried unsuccessfully using:

emptyRow = WorksheetFunction.CountA(Range("A:Z")) + 1

Would it instead be something like:

a = WorksheetFunction.CountA(Range("A:A")) + 1
b = WorksheetFunction.CountA(Range("B:B")) + 1
...

then emptyRow = (some code for max value of a:z)

[westconn1]

you could try usedrange.rows.count property of the sheet object, but this too can lie sometimes, especially if you have empty rows at the top of the sheet, or some rows have been deleted

[timh1234]

Yeah, data gets deleted from rows in this workbook regularly, so other rows will have been used, but then become empty.

Any other ideas?

[westconn1]

for col = 1 to 26
  if lastrow < cells(rows.count, col) then lastow = cells(rows.count, col)
next

[timh1234]

This code doesn't work for me I'm afraid. I have column headers in Row 1. I had data in Row 2, Column B. I ran the userform to add a new entry, hoping that it would place it in Row 3 when saved, but it actually entered it into Row 1, overwriting the column headers.

From your code, I added a further line:

emptyRow = lastrow + 1

Is this where I am going wrong? (By the way, I corrected the spelling error in the code, so that is not the issue.)

[westconn1]

By the way, I corrected the spelling error in the code,

good you read it

for some reason i only wrote half the code (brain dead yseterday)
l

astrow = 1
    For col = 1 To 26
      If lastrow < Cells(Rows.Count, col).End(xlUp).Row Then lastrow = Cells(Rows.Count, col).End(xlUp).Row
    Next

[timh1234]

Thanks very much, I'll try that, and I'll correct this spelling mistake too

[timh1234]

This works perfectly. Thank you.

[westconn1]

and I'll correct this spelling mistake too

not spelling /typing this time, selecting to copy missed the first character