Get next empty cell in column B and return the value in column A

[skacutter]

In VBA I need to Get the next empty cell in column B and return the value in column A. Column A has a unique ID number so the entire table has values for column A. I need the first row where B is empty to return to a text-box the value in A and then fill the rest of the columns in that row.

 

[Joe4]

I need to Get the next empty cell in column B

Starting from where?
A certain row in column B?

 

[skacutter]

Starting from where?
A certain row in column B?

start from B2.

 

[Rick Rothstein]

start from B2.

I assume cell B1 contains a header, so it is not blank and I also assume Column B cells do not contain formulas. If those two assumptions are valid, then this single line of code will give you the value you seek...

ValueInColA = Columns("B").SpecialCells(xlBlanks)(1).Offset(, 1).Value

 

[Joe4]

Rick,

Nice solution! I like the logic!

However, you have a minor typo in there. It should be:
.Offset(, -1)
instead of
.Offset(, 1)
otherwise you are returning the value from column C, not column A.

 

[skacutter]

Yes that worked with the offset -1. Thank you both so much

 

[Rick Rothstein]

Rick,

Nice solution! I like the logic!

However, you have a minor typo in there. It should be:
.Offset(, -1)
instead of
.Offset(, 1)
otherwise you are returning the value from column C, not column A.

Whoops! Thanks for catching that.

 

[skacutter]

Sorry, One last question. We found the blank in column B and returned the value in column A..... Now how do I get a handle on that row so I can fill in the rest of the columns?

 

[Joe4]

If you want to capture the row number that blank is found on, you can do so like this:

myRow = Columns("B").SpecialCells(xlBlanks)(1).Row