Remove last n rows

[Colin Hayes]

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

 

[Mike H]

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]

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.

 

[Ken]

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

 

[Colin Hayes]

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

 

[JoeU2004]

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]

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

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

<a06ac1aa-8294-4c25-8f25-d8bbbab11a89@w40g2000yqd.googlegroups.com>, Ken

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