Find the first open cell in a row

  • Thread starter Thread starter S.H.
  • Start date Start date
S

S.H.

I am trying to create a macro to update a spreadsheet
automatically. My problem is that I want the new data to
go to the first open cell in row 35 every time I run the
macro. What command will find that cell?
Thanks in advance for your help.
S.H.
 
Hi Try This...

Sub GetFirstCell()
Dim x as integer
x = 1
Do Until cells(35,x) = ""
x = x + 1
Loop
Msgbox "First Empty Cell is: " & x
End Sub
 
Hi SH

Try

Sub test()
Dim E As Range
Set E = Rows(35).Find(What:="")
MsgBox E.Address
End Sub
 
How about:

Option Explicit
Sub test()
Dim E As Range
With Rows(35)
Set E = .Find(What:="", after:=.Cells(.Cells.Count))
End With
If E Is Nothing Then
MsgBox "no blank cells"
Else
MsgBox E.Address
End If
End Sub
 
Another option:

Sub FindFirstBlank()
'Leo Heuser, 6 Nov 2003
Dim FirstBlank As Range

Set FirstBlank = _
ActiveSheet.Rows(35).SpecialCells(xlCellTypeBlanks).Cells(1, 1)

End Sub


--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
Alan Beban said:
Doesn't work if Cell E1 is blank.
Click to expand...

Or A35 ? <g> Ok, right you are Alan.

Sub test()
Dim E As Range
Set E = Cells(35, 1)
If E.Value <> "" Then Set E = Rows(35).Find(What:="")
MsgBox E.Address
End Sub
 
after:=.Cells(.Cells.Count))

Nice trick Dave. Thanks.

Best wishes Harald
Followup to newsgroup only please
 
In followup to the message yesterday. I appreciate your
help. Two of these work well but I guess I should have
stated my question differently since now I would like to
select the open cell in row 35. Thank you greatly for all
your help.
 
Open cell? What's that?

Alan Beban

S.H said:
In followup to the message yesterday. I appreciate your
help. Two of these work well but I guess I should have
stated my question differently since now I would like to
select the open cell in row 35. Thank you greatly for all
your help.
Click to expand...
 
I want to go to the first blank cell in row 35 so that I
can insert data into it each week. The reason I do this is
to keep a ongoing 12 week average of the data I report on.
 
how about this modified code that Dave posted

Option Explicit
Sub test()
'go to the first open cell in row 35
Dim E As Range
With Rows(35)
Set E = .Find(What:="", after:=.Cells(.Cells.Count))
End With
If E Is Nothing Then
MsgBox "no blank cells"
Else

'will show the address
'MsgBox E.Address

'this will select the cell
E.Select
End If
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
This works great to give me a message stating the blank
cell but, what do I add this statment so my cursur will go
there? I know it is something real easy I just can't get
it to work.
Sub test()
Dim E As Range
With Rows(35)
Set E = .Find(What:="", after:=.Cells(.Cells.Count))
End With
If E Is Nothing Then
E.Select
Else
MsgBox E.Address
End If
End Sub
 
I see. I hope. <g>. Replace
MsgBox E.Address
with
E.Select
or/and something like
E.Value = "I am entering my lyrics"
in any of the provided solutions.
 
Back
Top