Better way to select Cell in Range
 

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

Better way to select Cell in Range
 

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

Better way to select Cell in Range
 

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

--
Gary''s Student - gsnu200908

Better way to select Cell in Range
 

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

Better way to select Cell in Range
 

Maybe this?

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

--
Rick (MVP - Excel)


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

Better way to select Cell in Range
 

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

Better way to select Cell in Range
 

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

Better way to select Cell in Range
 

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

Better way to select Cell in Range
 

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

Better way to select Cell in Range
 

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

Better way to select Cell in Range
 

Very nice Mike, very nice.
--
Gary''s Student - gsnu200908


"Mike H" wrote:

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