Deleting row using vb code?
 

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?
--
hol

Deleting row using vb code?
 

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

--

Regards
Roger Govier

"hol" wrote in message
...
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?
--
hol

Deleting row using vb code?
 

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.
--
hol


"Roger Govier" wrote:

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

--

Regards
Roger Govier

"hol" wrote in message
...
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?
--
hol

Deleting row using vb code?
 

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.
--

Regards
Roger Govier

"hol" wrote in message
...
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.
--
hol


"Roger Govier" wrote:

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

--

Regards
Roger Govier

"hol" wrote in message
...
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?
--
hol

Deleting row using vb code?
 

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


"Roger Govier" wrote:

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.
--

Regards
Roger Govier

"hol" wrote in message
...
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.
--
hol


"Roger Govier" wrote:

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

--

Regards
Roger Govier

"hol" wrote in message
...
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?
--
hol