Better way to select Cell in Range

[Hal]

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

 

[Barb Reinhardt]

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.

 

[Gary''s Student]

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

 

[Mike H]

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

 

[Rick Rothstein]

Maybe this?

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

 

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

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

 

[muddan madhu]

Sub test2()

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

End Sub

 

[Rick Rothstein]

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

 

[John_John]

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" έγγÏαψε:

 

[Gary''s Student]

Very nice Mike, very nice.