How to Find the First Blank row on my Excel Sheet usin VBA

[louvalterry]

I have been looking for a solution to my problem now for a couple of days. I need some VBA Excel code the finds the first blank row in my sheet. I then want to pastespecial a row from another sheet which I copied. The code I found (Attached) pastes the new row at the very bottom of the sheet (row 1048576) and then upwards. This is not what I need. I need to paste in on row 10 if rows 1 - 9 already have data.

/ Begin Code

Private Sub cmdDeleteRow_Click()
Dim ws As Worksheet
Dim lRow As Long
Dim LastRow As Long

If Not IsInRange(ActiveCell, ActiveSheet.Range("ForecastTable")) Then
MsgBox "You can not delete lines here.", vbCritical
Exit Sub
End If
Application.ScreenUpdating = False
ActiveSheet.Unprotect
MsgBox "You are about to copy the deleted data to the Audit tab "
ActiveCell.EntireRow.Copy
ActiveSheet.Unprotect
Sheets("Audit").Select
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Audit").Range("A" & LastRow).PasteSpecial xlPasteValues
Sheets("Monthly Staffing_F").Select

ActiveCell.EntireRow.Delete
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

/ End Code

[jolivanes]

Where does it paste if rows 1 to 15 have data? In Row 16?
The line

LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
goes to the first empty cell in column A

BTW, you have

ActiveSheet.Unprotect
twice

Could you please put code tags around your code.
Highlight your code and click on the #

[louvalterry]

jolivanes

For you question, If rows 1 - 15 have data then paste in row 16.


LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1

This code is pasting to random rows on the spreadsheet. I just executed the code twice today. The first copied row was pasted on Row 663. The second was pasted on row 662

I am using Excel 2010. Don't know if this would matter.

[louvalterry]

Sorry forgot to add that the Audit sheet has one heading row and 1 data row before I ran the test above.

[jolivanes]

It looks like your cells are not empty. There might be nothing showing but they could have some non visible characters in them.
I tried part of your above code and it works fine.
Could you delete all the rows below your last entry and then see what it does.