Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code for Deleting a Row | Excel Worksheet Functions | |||
deleting selected worksheets using vb code | Excel Discussion (Misc queries) | |||
Deleting Workbook_Open code | Excel Discussion (Misc queries) | |||
Deleting Code from VBA | Excel Discussion (Misc queries) | |||
Deleting ALL duplicates using Pearson's code | Excel Worksheet Functions |