If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Remove last n rows
HI I have an excel problem I hope someone can help with. I need a small macro which , via popup , will allow me to remove the last n rows of a worksheet. Ideally the popup would ask 'Remove how many rows from bottom of sheet?'. I could then input the number and that many would be deleted from the bottom. Grateful for any assistance. Best Wishes |
#2
|
|||
|
|||
Remove last n rows
Hi,
Right click your sheet tab, view code and paste this in and run it Sub Del_Last_N() response = CLng(InputBox("How many rows to delete?")) If response = vbNullString Or Not IsNumeric(response) Then Exit Sub lastrow = ActiveSheet.UsedRange.Rows.Count If response lastrow Then MsgBox "There are only " & lastrow & " rows in the worksheet" Exit Sub End If Rows(lastrow - (response - 1)).Resize(response).Delete End Sub Mike "Colin Hayes" wrote: HI I have an excel problem I hope someone can help with. I need a small macro which , via popup , will allow me to remove the last n rows of a worksheet. Ideally the popup would ask 'Remove how many rows from bottom of sheet?'. I could then input the number and that many would be deleted from the bottom. Grateful for any assistance. Best Wishes |
#3
|
|||
|
|||
Remove last n rows
In article , Mike H
writes Hi, Right click your sheet tab, view code and paste this in and run it Sub Del_Last_N() response = CLng(InputBox("How many rows to delete?")) If response = vbNullString Or Not IsNumeric(response) Then Exit Sub lastrow = ActiveSheet.UsedRange.Rows.Count If response lastrow Then MsgBox "There are only " & lastrow & " rows in the worksheet" Exit Sub End If Rows(lastrow - (response - 1)).Resize(response).Delete End Sub Mike Hi Mike Thanks for that. I tried it out and it looks good. I used it as a macro rather than placing in the sheet tab. I see it won't accept a zero entry to the input box and crashes out when '0' is entered. Can it be fixed so that it will work on the occasions when I don't want to delete any from the bottom , and want to enter zero deletions in the popup? Thanks. "Colin Hayes" wrote: HI I have an excel problem I hope someone can help with. I need a small macro which , via popup , will allow me to remove the last n rows of a worksheet. Ideally the popup would ask 'Remove how many rows from bottom of sheet?'. I could then input the number and that many would be deleted from the bottom. Grateful for any assistance. Best Wishes |
#4
|
|||
|
|||
Remove last n rows
Private Sub CommandButton1_Click()
response = CLng(InputBox("How many rows to delete?")) If response = vbNullString Or response = 0 Then Exit Sub lastrow = ActiveSheet.UsedRange.Rows.Count If response lastrow Then MsgBox "There are only " & lastrow & " rows in the worksheet" Exit Sub End If Rows(lastrow - (response - 1)).Resize(response).Delete End Sub This is a pretty small changeto Mike's macro; maybe you could just not run the macro if you don't want to delete anything? Ken On Jun 11, 4:26*pm, Colin Hayes wrote: In article , Mike H writes Hi, Right click your sheet tab, view code and paste this in and run it Sub Del_Last_N() response = CLng(InputBox("How many rows to delete?")) If response = vbNullString Or Not IsNumeric(response) Then Exit Sub lastrow = ActiveSheet.UsedRange.Rows.Count If response lastrow Then * *MsgBox "There are only " & lastrow & " rows in the worksheet" * *Exit Sub End If Rows(lastrow - (response - 1)).Resize(response).Delete End Sub Mike Hi Mike Thanks for that. I tried it out and it looks good. I used it as a macro rather than placing in the sheet tab. I see it won't accept a zero entry to the input box and crashes out when '0' is entered. Can it be fixed so that it will work on the occasions when I don't want to delete any from the bottom , and want to enter zero deletions in the popup? Thanks. "Colin Hayes" wrote: HI I have an excel problem I hope someone can help with. I need a small macro which , via popup , will allow me to remove the last n rows of a worksheet. Ideally the popup would ask 'Remove how many rows from bottom of sheet?'. I could then input the number and that many would be deleted from the bottom. Grateful for any assistance. Best Wishes- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#5
|
|||
|
|||
Remove last n rows
In article
, Ken writes Private Sub CommandButton1_Click() response = CLng(InputBox("How many rows to delete?")) If response = vbNullString Or response = 0 Then Exit Sub lastrow = ActiveSheet.UsedRange.Rows.Count If response lastrow Then MsgBox "There are only " & lastrow & " rows in the worksheet" Exit Sub End If Rows(lastrow - (response - 1)).Resize(response).Delete End Sub This is a pretty small changeto Mike's macro; maybe you could just not run the macro if you don't want to delete anything? Ken HI Ken OK thanks for your help. It now works when zero is entered. I'm adding this code into an existing macro that I run frequently. Sometimes I'll need to use it , others not. For this reason it's useful to have the zero option. The problem I encounter now though is that it exits the whole macro when zero is entered , rather than continuing on through to the rest of the macro. When I enter a number and some rows are removed , it then continues with the rest of my existing macro , which is what I want. Hmmm... Best Wishes On Jun 11, 4:26*pm, Colin Hayes wrote: In article , Mike H writes Hi, Right click your sheet tab, view code and paste this in and run it Sub Del_Last_N() response = CLng(InputBox("How many rows to delete?")) If response = vbNullString Or Not IsNumeric(response) Then Exit Sub lastrow = ActiveSheet.UsedRange.Rows.Count If response lastrow Then * *MsgBox "There are only " & lastrow & " rows in the worksheet" * *Exit Sub End If Rows(lastrow - (response - 1)).Resize(response).Delete End Sub Mike Hi Mike Thanks for that. I tried it out and it looks good. I used it as a macro rather than placing in the sheet tab. I see it won't accept a zero entry to the input box and crashes out when '0' is entered. Can it be fixed so that it will work on the occasions when I don't want to delete any from the bottom , and want to enter zero deletions in the popup? Thanks. "Colin Hayes" wrote: HI I have an excel problem I hope someone can help with. I need a small macro which , via popup , will allow me to remove the last n rows of a worksheet. Ideally the popup would ask 'Remove how many rows from bottom of sheet?'. I could then input the number and that many would be deleted from the bottom. Grateful for any assistance. Best Wishes- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
|
|||
|
|||
Remove last n rows
"Colin Hayes" wrote:
Ken writes: If response = vbNullString Or response = 0 Then Exit Sub [....] Rows(lastrow - (response - 1)).Resize(response).Delete [....] The problem I encounter now though is that it exits the whole macro when zero is entered , rather than continuing on through to the rest of the macro. Simply change "exit sub" to "goto continue1" and add the label "continue1:" after Rows.Delete statement. Alternatively, change the logic as follows: If response vbNullString and response 0 Then ....some code.... Rows(lastrow - (response - 1)).Resize(response).Delete End If .....rest of macro.... ----- original message ----- "Colin Hayes" wrote in message ... In article , Ken writes Private Sub CommandButton1_Click() response = CLng(InputBox("How many rows to delete?")) If response = vbNullString Or response = 0 Then Exit Sub lastrow = ActiveSheet.UsedRange.Rows.Count If response lastrow Then MsgBox "There are only " & lastrow & " rows in the worksheet" Exit Sub End If Rows(lastrow - (response - 1)).Resize(response).Delete End Sub This is a pretty small changeto Mike's macro; maybe you could just not run the macro if you don't want to delete anything? Ken HI Ken OK thanks for your help. It now works when zero is entered. I'm adding this code into an existing macro that I run frequently. Sometimes I'll need to use it , others not. For this reason it's useful to have the zero option. The problem I encounter now though is that it exits the whole macro when zero is entered , rather than continuing on through to the rest of the macro. When I enter a number and some rows are removed , it then continues with the rest of my existing macro , which is what I want. Hmmm... Best Wishes On Jun 11, 4:26 pm, Colin Hayes wrote: In article , Mike H writes Hi, Right click your sheet tab, view code and paste this in and run it Sub Del_Last_N() response = CLng(InputBox("How many rows to delete?")) If response = vbNullString Or Not IsNumeric(response) Then Exit Sub lastrow = ActiveSheet.UsedRange.Rows.Count If response lastrow Then MsgBox "There are only " & lastrow & " rows in the worksheet" Exit Sub End If Rows(lastrow - (response - 1)).Resize(response).Delete End Sub Mike Hi Mike Thanks for that. I tried it out and it looks good. I used it as a macro rather than placing in the sheet tab. I see it won't accept a zero entry to the input box and crashes out when '0' is entered. Can it be fixed so that it will work on the occasions when I don't want to delete any from the bottom , and want to enter zero deletions in the popup? Thanks. "Colin Hayes" wrote: HI I have an excel problem I hope someone can help with. I need a small macro which , via popup , will allow me to remove the last n rows of a worksheet. Ideally the popup would ask 'Remove how many rows from bottom of sheet?'. I could then input the number and that many would be deleted from the bottom. Grateful for any assistance. Best Wishes- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
|
|||
|
|||
Remove last n rows
HI All OK thanks for your help and tweaking. It's working perfectly now and fits in very nicely with the rest of the macro. This is how it ended up : (first part of existing macro) response = CLng(InputBox("How many rows to delete?")) If response = vbNullString Or response = 0 Then GoTo continue1 lastrow = ActiveSheet.UsedRange.Rows.Count If response lastrow Then MsgBox "There are only " & lastrow & " rows in the worksheet" Exit Sub End If Rows(lastrow - (response - 1)).Resize(response).Delete continue1: (rest of macro) Best Wishes In article , JoeU2004 writes "Colin Hayes" wrote: Ken writes: If response = vbNullString Or response = 0 Then Exit Sub [....] Rows(lastrow - (response - 1)).Resize(response).Delete [....] The problem I encounter now though is that it exits the whole macro when zero is entered , rather than continuing on through to the rest of the macro. Simply change "exit sub" to "goto continue1" and add the label "continue1:" after Rows.Delete statement. Alternatively, change the logic as follows: If response vbNullString and response 0 Then ....some code.... Rows(lastrow - (response - 1)).Resize(response).Delete End If ....rest of macro.... ----- original message ----- "Colin Hayes" wrote in message ... In article , Ken writes Private Sub CommandButton1_Click() response = CLng(InputBox("How many rows to delete?")) If response = vbNullString Or response = 0 Then Exit Sub lastrow = ActiveSheet.UsedRange.Rows.Count If response lastrow Then MsgBox "There are only " & lastrow & " rows in the worksheet" Exit Sub End If Rows(lastrow - (response - 1)).Resize(response).Delete End Sub This is a pretty small changeto Mike's macro; maybe you could just not run the macro if you don't want to delete anything? Ken HI Ken OK thanks for your help. It now works when zero is entered. I'm adding this code into an existing macro that I run frequently. Sometimes I'll need to use it , others not. For this reason it's useful to have the zero option. The problem I encounter now though is that it exits the whole macro when zero is entered , rather than continuing on through to the rest of the macro. When I enter a number and some rows are removed , it then continues with the rest of my existing macro , which is what I want. Hmmm... Best Wishes On Jun 11, 4:26 pm, Colin Hayes wrote: In article , Mike H writes Hi, Right click your sheet tab, view code and paste this in and run it Sub Del_Last_N() response = CLng(InputBox("How many rows to delete?")) If response = vbNullString Or Not IsNumeric(response) Then Exit Sub lastrow = ActiveSheet.UsedRange.Rows.Count If response lastrow Then MsgBox "There are only " & lastrow & " rows in the worksheet" Exit Sub End If Rows(lastrow - (response - 1)).Resize(response).Delete End Sub Mike Hi Mike Thanks for that. I tried it out and it looks good. I used it as a macro rather than placing in the sheet tab. I see it won't accept a zero entry to the input box and crashes out when '0' is entered. Can it be fixed so that it will work on the occasions when I don't want to delete any from the bottom , and want to enter zero deletions in the popup? Thanks. "Colin Hayes" wrote: HI I have an excel problem I hope someone can help with. I need a small macro which , via popup , will allow me to remove the last n rows of a worksheet. Ideally the popup would ask 'Remove how many rows from bottom of sheet?'. I could then input the number and that many would be deleted from the bottom. Grateful for any assistance. Best Wishes- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|