Finding the next empty cell in a row?

[Todd_M]

Hi-
Im using the following code to find the next empty cell in column "ac" and then listbox1 - listbox9 will list data from a userform in the cells to the right of column "ac".

For Each c In Worksheets("sheet1").Range("ac1:ac10000")
If c.Value = TextBox10.Text Then
c.Offset(0, 1).Value = TextBox1.Text
c.Offset(0, 2).Value = TextBox2.Text
c.Offset(0, 3).Value = TextBox3.Text
c.Offset(0, 4).Value = TextBox4.Text
c.Offset(0, 5).Value = TextBox5.Text
c.Offset(0, 6).Value = TextBox6.Text
c.Offset(0, 7).Value = TextBox7.Text
c.Offset(0, .Value = TextBox8.Text
c.Offset(0, 9).Value = TextBox9.Text



'End If
'Next c
All the data list using the offset method as it should, however I have to tell wich row to list it in by directing the row# in listbox10. The user wont be able to see which nextrow is not being used, so I need some help on finding the first blank cell in column "ac". Thanks

[NateO]

Could try:

Sub gtIt()
[ac1].End(xlDown).Offset(1, 0).Select
End Sub

[Juan Pablo González]

I'd recommend instead:

[ac65536].End(xlUp).Offset(1, 0).Select

Also, there's no need to select anything here, but, that works.

[NateO]

Well, I concur, that Juan's code works pretty gosh-darned well with respect to selecting the [very] last empty row, but not necessarily the "first blank cell" (if you have blank cells). I may have taken you too literally...

Ahh, the semantics of Excel (and her lovers).

But it seems I did not answer the question to return the row number. The following ought to work:

sub gtit2()
Dim LastRow As Integer
LastRow = [ac1].End(xlDown).Offset(1, 0).row
end sub

You can then call LastRow as your row number.
_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-03-20 21:22 ]

[Russell Hauf]

You could also tidy up your code with a loop:

'...
With c
    If .Value = TextBox10.Text Then 

        For i = 1 to 9
           .Offset(0, i).Value = Controls("TextBox" & i).Text
        Next i
    End if
End With
'...

-rh


[ This Message was edited by: Russell Hauf on 2002-03-20 22:14 ]

[Dinictus]

Or this:

Range("A1").Activate
For r = 1 To 65536
If Not IsEmpty(ActiveCell) Then
ActiveCell.Offset(1, 0).Activate
Else
If IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) And _
IsEmpty(ActiveCell.Offset(0, 2)) And IsEmpty(ActiveCell.Offset(0, 3)) And _
IsEmpty(ActiveCell.Offset(0, 4)) And IsEmpty(ActiveCell.Offset(0, 5)) And _
IsEmpty(ActiveCell.Offset(0, 6)) And IsEmpty(ActiveCell.Offset(0, 7)) And _
IsEmpty(ActiveCell.Offset(0, ) And IsEmpty(ActiveCell.Offset(0, 9)) _
Then