Finding 1st empty row

[JaneB19]

This is probably really basic and/or really simple! But it's got me baffeled!

What I would like to do is to insert a new record on the 1st available row. I have previously tried setting the row number that it should start from and then incrementing the new row number but it just reset itself each time the button was clicked :-( The insert works it's just the finding the empty row that doesn't.

Can anybody help me with this PLEASE!!!!!

Thank you


[PC2]

 

[JohnAcc]

Hi Jane,

Try the following:

Sub test()
For i = 1 To 65536
If IsEmpty(Cells(i, "A&quot) Then
Cells(i, "A&quot.EntireRow.Insert
Exit Sub
End If
Next i
End Sub

Cheers

John

 

[Bowers74]

One way to return the next row that is available (if all of the cells in Column A in the table are filled) is:

Dim LastRow As Integer
LastRow = [A65536].End(xlUp).Row
Cells(LastRow + 1, 1).Select

If you want to enter the data into the first empty cell and all of the cells in Column A are not filled then use John's solution. ;-)

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JohnAcc]

Or.....

Sub test()
Range("A1&quot.End(xlDown).EntireRow.Insert
End Sub

 

[Bowers74]

Actually, if you have blanks in the table's A Column and you want to select the first empty cell, you could use a variation of my last post:

Dim LastRow As Integer
LastRow = [A1].End(xlDown).Row
Cells(LastRow - 1, 1).Select

Good Luck!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Hi,

I agree in principle but disagree on the specifics, Mike.

Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

1. Row value variables should be Long and NOT Integer
2. You should avoid using the Activate an Select Methods for Excel objects whenever possible -- see my FAQ faq707-4105



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JaneB19]

Thank you all of you for your help.

I've now got the coding working, with a bit of tweeking

Thanks again

Jane

[PC2]

 

[navyguy]

Why are row variables dimensioned as long?

 

[SkipVought]

There are 65536 rows in Excel sheets. The MAX Integer is 32767.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com