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 -