Deleting row using vb code?

[hol]

I have the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row >= 100 Then Rows(3).Delete
End Sub

Using this code how can I then insert a row at the bottom of the spreadsheet?

 

[Roger Govier]

Hi

Try amending the code to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row >= 100 Then
Rows(3).Delete
Target.EntireRow.Insert
End If
End Sub

I'm not exactly certain where you want your row inserted, so you might have
to amend the line
Target.EntireRow.Insert
to
Target.Offset(-1,0).EntireRow.Insert
to get your insertion in the correct place

 

[hol]

Thanks Roger,
I need to insert the new row right at the bottom of the spreadsheet ie
65,000+ what ever the total number of rows in a spreadsheet.

 

[Roger Govier]

Hi

I don't understand.
Why would you want to be inserting a row at 65536?
Row 65536 will always be there.
If you delete rows in a spreadsheet, it is the contents of the row that
disappear, and the rows move up. Excel retains the full number of rows in
the sheet at all times.

Perhaps you can explain a little more what you are trying to do.
I had thought, that perhaps you had some totals in row 101, and when you got
to row 100, you wanted to delete row 3, but have a new row available for
data entry above your total.

 

[hol]

Ah! Thanks Roger,
You have answered the question in your reply. It makes sense now thank you
very much.