Find first blank cell
 

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,
--
Jim T

Find first blank cell
 

On Jan 4, 2:08*pm, Jim Tibbetts
wrote:
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,
--
Jim T

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

Find first blank cell
 

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.



--


Gary


"Jim Tibbetts" wrote in message
...
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,
--
Jim T

Find first blank cell
 

Try this

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

FWIW, my grandfather was a Tibbetts.
--
HTH,
Barb Reinhardt


"Jim Tibbetts" wrote:

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,
--
Jim T

Find first blank cell
 

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 T


" wrote:

On Jan 4, 2:08 pm, Jim Tibbetts
wrote:
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,
--
Jim T

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

Find first blank cell
 

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 T


"Barb Reinhardt" wrote:

Try this

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

FWIW, my grandfather was a Tibbetts.
--
HTH,
Barb Reinhardt


"Jim Tibbetts" wrote:

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,
--
Jim T

Find first blank cell
 

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,
--
Jim T


"Gary Keramidas" wrote:

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.



--


Gary


"Jim Tibbetts" wrote in message
...
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,
--
Jim T

Find first blank cell
 

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

Barb Reinhardt



"Jim Tibbetts" wrote:

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 T


" wrote:

On Jan 4, 2:08 pm, Jim Tibbetts
wrote:
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,
--
Jim T

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

Find first blank cell
 

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


"Barb Reinhardt" wrote:

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

Barb Reinhardt



"Jim Tibbetts" wrote:

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 T


" wrote:

On Jan 4, 2:08 pm, Jim Tibbetts
wrote:
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,
--
Jim T

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

Find first blank cell
 

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

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,
--
Jim T

--

Dave Peterson

Find first blank cell
 

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


"Dave Peterson" wrote:

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

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,
--
Jim T

--

Dave Peterson