Better way to select Cell in Range

  • Thread starter Thread starter Hal
  • Start date Start date
H

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
 
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.
 
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
 
Maybe this?

Sub SelectBlank()
On Error Resume Next
Range("K2:V2").SpecialCells(xlCellTypeBlanks)(1).Select
End Sub
 
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
 
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
 
Sub test2()

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

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