I have the range K2:V2. I want to start at K2 and check if the cell is empty.
If so select it, if not, check L2, M2 . . . V2 until the first empty cell in
this range is found, and then select it. The code below will work but I would
like to have something more professional.

Signed, Novice at work


If Range("K2").Value = "" Then
Range("K2").Select
Elseif Range("L2").Value = "" Then
Range("L2").Select

Dim myRange as Excel.Range
Dim r as Excel.Range

set myRange = Range("K2:V2")

for each r in myRange
if r.value = "" then
r.select
end if
next r

That's how you do it, but selection really slows down execution.
--
HTH,

Barb Reinhardt



"Hal" wrote:

> I have the range K2:V2. I want to start at K2 and check if the cell is empty.
> If so select it, if not, check L2, M2 . . . V2 until the first empty cell in
> this range is found, and then select it. The code below will work but I would
> like to have something more professional.
>
> Signed, Novice at work
>
>
> If Range("K2").Value = "" Then
> Range("K2").Select
> Elseif Range("L2").Value = "" Then
> Range("L2").Select
> .
> .
> .
> Else
> Range("V2").Select

Sub Macro1()
Range("K2").End(xlToRight).Offset(0, 1).Select
End Sub

--
Gary''s Student - gsnu200908

Hi,

Try this but note I didn't trap for no empty cells in the range which would
throw an error

Dim rng As Range
Set rng = Range("K2:V2")
rng.Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select

Mike

"Hal" wrote:

> I have the range K2:V2. I want to start at K2 and check if the cell is empty.
> If so select it, if not, check L2, M2 . . . V2 until the first empty cell in
> this range is found, and then select it. The code below will work but I would
> like to have something more professional.
>
> Signed, Novice at work
>
>
> If Range("K2").Value = "" Then
> Range("K2").Select
> Elseif Range("L2").Value = "" Then
> Range("L2").Select
> .
> .
> .
> Else
> Range("V2").Select

Maybe this?

Sub SelectBlank()
On Error Resume Next
Range("K2:V2").SpecialCells(xlCellTypeBlanks)(1).Select
End Sub

--
Rick (MVP - Excel)


"Hal" <(E-Mail Removed)> wrote in message
news:5A3F8D8C-7A9A-4DCC-812C-(E-Mail Removed)...
>I have the range K2:V2. I want to start at K2 and check if the cell is
>empty.
> If so select it, if not, check L2, M2 . . . V2 until the first empty cell
> in
> this range is found, and then select it. The code below will work but I
> would
> like to have something more professional.
>
> Signed, Novice at work
>
>
> If Range("K2").Value = "" Then
> Range("K2").Select
> Elseif Range("L2").Value = "" Then
> Range("L2").Select
> .
> .
> .
> Else
> Range("V2").Select

Includes a trap for no empty cells

Dim rng As Range
On Error GoTo Getmeout
Set rng = Range("K2:V2")
rng.Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select
Exit Sub
Getmeout:
MsgBox "No empty cells in range"

Mike

"Mike H" wrote:

> Hi,
>
> Try this but note I didn't trap for no empty cells in the range which would
> throw an error
>
> Dim rng As Range
> Set rng = Range("K2:V2")
> rng.Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select
>
> Mike
>
> "Hal" wrote:
>
> > I have the range K2:V2. I want to start at K2 and check if the cell is empty.
> > If so select it, if not, check L2, M2 . . . V2 until the first empty cell in
> > this range is found, and then select it. The code below will work but I would
> > like to have something more professional.
> >
> > Signed, Novice at work
> >
> >
> > If Range("K2").Value = "" Then
> > Range("K2").Select
> > Elseif Range("L2").Value = "" Then
> > Range("L2").Select
> > .
> > .
> > .
> > Else
> > Range("V2").Select

GS,

I would avoid this method because it fails if all cells are empty and
selects a cell outside the range if all cells are populated.

Mike

"Gary''s Student" wrote:

> Sub Macro1()
> Range("K2").End(xlToRight).Offset(0, 1).Select
> End Sub
>
> --
> Gary''s Student - gsnu200908

Sub test2()

Range("K2").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select
Loop

End Sub


On Nov 14, 12:43*am, Hal <H...@discussions.microsoft.com> wrote:
> I have the range K2:V2. I want to start at K2 and check if the cell is empty.
> If so select it, if not, check L2, M2 . . . V2 until the first empty cellin
> this range is found, and then select it. The code below will work but I would
> like to have something more professional.
>
> Signed, Novice at work
>
> * * If Range("K2").Value = "" Then
> * * *Range("K2").Select
> * * Elseif Range("L2").Value = "" Then
> * * *Range("L2").Select
> * * .
> * * .
> * * .
> * * Else
> * * *Range("V2").Select

Here is a patch for Gary''s Student's method...

Sub Macro1()
On Error Resume Next
Intersect(Range("K2").End(xlToRight).Offset(0, 1), Columns("K:V")).Select
End Sub

--
Rick (MVP - Excel)


"Mike H" <(E-Mail Removed)> wrote in message
news:88F22084-CAE4-4CC7-B01E-(E-Mail Removed)...
> GS,
>
> I would avoid this method because it fails if all cells are empty and
> selects a cell outside the range if all cells are populated.
>
> Mike
>
> "Gary''s Student" wrote:
>
>> Sub Macro1()
>> Range("K2").End(xlToRight).Offset(0, 1).Select
>> End Sub
>>
>> --
>> Gary''s Student - gsnu200908

Hi all!

On Error Resume Next
Range("K2:V2").SpecialCells(4)(1).Select
'or
Range("K2:V2").Find("").Select
If Err Then MsgBox "No cells were found.", vbExclamation

Ο χÏ�ήστης "Mike H" Î*γγÏ�αψε:

> Includes a trap for no empty cells
>
> Dim rng As Range
> On Error GoTo Getmeout
> Set rng = Range("K2:V2")
> rng.Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select
> Exit Sub
> Getmeout:
> MsgBox "No empty cells in range"
>
> Mike
>
> "Mike H" wrote:
>
> > Hi,
> >
> > Try this but note I didn't trap for no empty cells in the range which would
> > throw an error
> >
> > Dim rng As Range
> > Set rng = Range("K2:V2")
> > rng.Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select
> >
> > Mike
> >
> > "Hal" wrote:
> >
> > > I have the range K2:V2. I want to start at K2 and check if the cell is empty.
> > > If so select it, if not, check L2, M2 . . . V2 until the first empty cell in
> > > this range is found, and then select it. The code below will work but I would
> > > like to have something more professional.
> > >
> > > Signed, Novice at work
> > >
> > >
> > > If Range("K2").Value = "" Then
> > > Range("K2").Select
> > > Elseif Range("L2").Value = "" Then
> > > Range("L2").Select
> > > .
> > > .
> > > .
> > > Else
> > > Range("V2").Select