Find next blank cell within a RANGE of a column.

[Gashmore]

I have found several techniques for finding the next blank cell in a column but none to limit the search to a range within a column.

The specifics: The worksheet is for scoring a sailboat race. There is a userform for entering the boat specifics into a table that consist of the entry #, boat name, class, handicap, etc.

The table starts at Row 11 and Column A is pre-loaded with Entry numbers from 1 to 30. I want to initialize the form with the next available Entry#. I want to search the range "BoatNames" ( B11:B31) for the next unused cell then load the form text box Entry_Number with the value in column A.

 

[dreid1011]

I have found several techniques for finding the next blank cell in a column but none to limit the search to a range within a column.

The specifics: The worksheet is for scoring a sailboat race. There is a userform for entering the boat specifics into a table that consist of the entry #, boat name, class, handicap, etc.

The table starts at Row 11 and Column A is pre-loaded with Entry numbers from 1 to 30. I want to initialize the form with the next available Entry#. I want to search the range "BoatNames" ( B11:B31) for the next unused cell then load the form text box Entry_Number with the value in column A.

Welcome to the board.

Will there ever be any information in Column B below row 31?

 

[Gashmore]

Welcome to the board.

Will there ever be any information in Column B below row 31?

Right now I have sized the sheet for 20 boats but it could get larger.

 

[dreid1011]

Right now I have sized the sheet for 20 boats but it could get larger.

That doesn't answer the question though. Is there any data on the sheet below the last possible boat name in Column B? Other tables/data etc?

 

[dreid1011]

Here are two alternatives to find the last used row, then add 1 to get the blank row beyond.

Sub FindNxtBlank()
Dim ws As Worksheet
Dim r As Long
Set ws = ActiveSheet

'This will find the last used row on the sheet starting at the bottom and looking up.
r = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1

'This will find the last used row in the range starting from B11 and looking down.
r = ws.Range("B11").End(xlDown).Row + 1

Then you can use r to identify the row in Column A to use to populate the userform text box.

 

[Gashmore]

That doesn't answer the question though. Is there any data on the sheet below the last possible boat name in Column B? Other tables/data etc?

Oh! No. The table is the end of the sheet.

 

[dreid1011]

Oh! No. The table is the end of the sheet.

Ok, then the first option from post #5 should do the trick just fine.. so probably something like this:

Dim ws As Worksheet
Dim r As Long
Set ws = ActiveSheet
r = ws.Cells(Rows.Count, "B").End(xlUp).Row + 1
UserForm1.TextBox1.Text = CStr(Range("A" & r).Value)

 

[Gashmore]

That doesn't answer the question though. Is there any data on the sheet below the last possible boat name in Column B? Other tables/data etc?

Thank you! That was far simpler than what I ws attempting.

 

[dreid1011]

Thank you! That was far simpler than what I ws attempting.

You're welcome. Happy to help.