Find first blank cell

[Jim Tibbetts]

Cell A3 has data in it. How do I go to the first empty cell to the right so I
can paste data? I have this code and it works if there is data in cell B3,
but doesn't work if B3 is blank.

Range("A3").End(xlToRight).Offset(0, 1).Select

Thanks for any help,

 

[crferguson]

Cell A3 has data in it. How do I go to the first empty cell to the right so I
can paste data? I have this code and it works if there is data in cell B3,
but doesn't work if B3 is blank.

Range("A3").End(xlToRight).Offset(0, 1).Select

Thanks for any help,

How about using the UsedRange property to get the column count...

Range("A3").Offset(0, ActiveSheet.UsedRange.Columns.Count).Select

Your code was bombing because it was trying to first reference a
column that didn't exist (there is no xlToEnd when there's only one
cell used).

 

[Gary Keramidas]

if you only care if b3 is left blank, then
Range("A3").offset(,1).End(xlToRight).Offset(0, 1).Select
but you have to watch out because the end(right) will take you to the last
column and the offset(0,1) with give you an error because it's non-existent.

 

[Barb Reinhardt]

Try this

Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).Select

FWIW, my grandfather was a Tibbetts.

 

[Jim Tibbetts]

Thanks for the reply. More info was needed. There is data in Col A, then
several blank columns then data again. Your code found the first empty column
to the right of the last column with data in it (which I think is what it is
supposed to do).

 

[Jim Tibbetts]

Hi Barb - Thanks for the reply. More info was needed. There is data in Col A,
then
several blank columns then data again. Your code found the first empty column
to the right of the last column with data in it (which I think is what it is
supposed to do).

P.S. Where was your grandfather from?

 

[Jim Tibbetts]

Actually, if B3 is blank, I want to paste data into it. If it is not, I want
to find the next blank cell in the row and paste data there.

Thanks,

 

[Barb Reinhardt]

I want to say he was from Temple Maine, but I'm not certain.

Barb Reinhardt

 

[Jim Tibbetts]

Barb - No relation. There are alot of Tibbetts' in the northeast. Our family
is from the midwest.

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim FirstEmpty As Range
Dim StartCell As Range

With ActiveSheet
Set StartCell = .Range("A3")
If IsEmpty(StartCell.Value) Then
Set FirstEmpty = StartCell
ElseIf IsEmpty(StartCell.Offset(0, 1).Value) Then
Set FirstEmpty = StartCell.Offset(0, 1)
Else
Set FirstEmpty = StartCell.End(xlToRight).Offset(0, 1)
End If
End With

MsgBox StartCell.Address(0, 0) & vbLf & FirstEmpty.Address(0, 0)

End Sub

 

[Jim Tibbetts]

Thanks Dave. I had to modify it to fit my needs, but you gave me the concept
I needed.