Next Blank Cell

  • Thread starter Thread starter mushy_peas
  • Start date Start date
Sub Test()
ActiveCell.Offset(0, 1).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select
Loop
End Sub

Try this one this will start in the Activecell
 
set cell = Cells(5,1).End(xltoRight)(1,2)

is a possibility.

It assumes there are entries in A and B.

If the cells will not have any embedded blank cells

set cell = Cells(5,256).End(xlToLeft)(1,2)

if column A could be blank you would have to check for that

if isempty(cells(5,1)) then
set cell = Cells(5,1)
else
set cell = Cells(5,256).End(xlToLeft)(1,2)
End if
 
One way:

Public Sub SelectFirstBlankCellInRow()
Const cnPARTICULARROW As Long = 2
With Cells(cnPARTICULARROW, 1).Resize(1, 2)
If Application.CountA(.Cells) < 2 Then
.Item(2 + IsEmpty(.Item(1).Value)).Select
Else
.Item(1).End(xlToRight).Offset(, 1).Select
End If
End With
End Sub

Change cnPARTICULARROW as needed to your particular row number.
 
if you have columns A5:C5 with values, Cells D5 blank, Cells E5:H5 with
values

then D5 would be an embedded blank cell and doing
Cells(5,1).end(xltoright)(1,2) would find D5.

Cells(5,256).End(xltoLeft)(1,2) would find I5.
 
And if anyone replies to the original message, I need one that selects the next
balnk row in a sheet.

Thanks

Chris
 
dont suppose you would be kind enough to explain the diff parts of
Cells(5,256).End(xltoLeft)(1,2) would find I5.

like what are 5,256, and End(xltoleft) and all the rest. i guess and
understand some of it, but want to be sure.

Cheers
 
Put some data in A5:C5
go to cell IV5 (iv = column 256, 5 = row 5)

Hit the end key then hit the left arrow key (sequentially - don't hold the
End key down). Where do you end up.

That is what the code does (the 1,2 on the end moves it right one to the
blank cell.)
 
Back
Top