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

Cheers


---
Message posted from http://www.ExcelForum.com/

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"mushy_peas " wrote in message ...
I need a macro that selects the next blank cell in a particaular ROW?

Cheers


---
Message posted from http://www.ExcelForum.com/

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

--
Regards,
Tom Ogilvy


mushy_peas wrote in message
...
I need a macro that selects the next blank cell in a particaular ROW?

Cheers


---
Message posted from http://www.ExcelForum.com/

cheers!! that worked a treat

--
Message posted from http://www.ExcelForum.com

actaully, what are embedded blank cells??

--
Message posted from http://www.ExcelForum.com

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.

In article ,
mushy_peas wrote:

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

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.

--
Regards,
Tom Ogilvy

mushy_peas wrote in message
...
actaully, what are embedded blank cells???


---
Message posted from http://www.ExcelForum.com/

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

Thanks

Chris

set cell = Cells(rows.count,1).end(xlup)(2)

--
Regards,
Tom Ogilvy

ChrisJForeman wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

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

--
Regards,
Tom Ogilvy


mushy_peas wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/