Next Blank Cell

M

mushy_peas

I need a macro that selects the next blank cell in a particaular ROW?

Cheers
 
R

Ron de Bruin

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
 
T

Tom Ogilvy

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
 
J

J.E. McGimpsey

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.
 
T

Tom Ogilvy

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.
 
C

ChrisJForeman

And if anyone replies to the original message, I need one that selects the next
balnk row in a sheet.

Thanks

Chris
 
M

mushy_peas

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
 
T

Tom Ogilvy

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.)
 
Top