Macro to Delete the last N rows

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

Hi

I need some help with a macro.

From time to time , I need to delete a varying number of rows from the
bottom of my worksheets.

I'd like to enter the number via popup on running it , and for it then
to delete the numbers of rows specified from the bottom.

Can someone advise?

Grateful for any help.
 
should do it

Sub deleterowsmsgboxSAS()
Dim nr As Long
Dim lr As Long
nr = InputBox("From which row to the bottom")
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
Rows(nr).Resize(lr).Delete
end Sub
 
Don said:
should do it

Sub deleterowsmsgboxSAS()
Dim nr As Long
Dim lr As Long
nr = InputBox("From which row to the bottom")
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
Rows(nr).Resize(lr).Delete
end Sub
Click to expand...

Hi Don

Ok thanks for that.

I'm getting a run time error in the

Rows(nr).Resize(lr).Delete

line.

If I amend it to

Rows(nr).Delete

Then it runs fine , but only deletes the single line entered of course.

I had in mind that I would enter say 200 into the box and it would
delete that many from the bottom , rather than entering the row number.
Would it be easier this way around?

Thanks for your help.
 
Hi Don

Ok thanks for that.

I'm getting a run time error in the

Rows(nr).Resize(lr).Delete

line.

If I amend it to

Rows(nr).Delete

Then it runs fine , but only deletes the single line entered of course.

I had in mind that I would enter say 200 into the box and it would
delete that many from the bottom , rather than entering the row number.
Would it be easier this way around?

Thanks for your help.
Click to expand...

I understood that you wanted to delete from the row number to the
bottom of the sheet. Before xl2007 there is 65536 rows so entering 17
would delete 65536-17
If you want something different, please be clear. Send file to
dguillett1 @gmail.com if desired.
 
Don said:
I understood that you wanted to delete from the row number to the bottom of the
sheet. Before
xl2007 there is 65536 rows so entering 17 would delete 65536-17 If you want
something different,
please be clear. Send file to dguillett1 @gmail.com if desired.
Click to expand...


Hi Don

Thanks for your email.

Sorry if it wasn't clear. I was hoping to enter the number in the input
box and then have it delete this many rows form the end of the active
rows.

For example , I have a worksheet of 300 rows with content. If I enter
200 into the box it would delete the last 200 , leaving the sheet with
the top 100 rows.

I tried it this way , and can't getting it working :


Sub A_Delete_Rows_From_End()

Dim lrow As Long
With ActiveSheet
lrow = Range("A" & Rows.Count).End(xlUp).Row
'Range("A1:A" & lrow).FillDown
End With

n = Application.InputBox("Delete how many rows from end of file?",
"Input", 0)

If n = False Then Exit Sub

Rows("(lrow):(lrow-n)").Select
Selection.Delete Shift:=xlUp

End Sub


It's the Rows("(lrow):(lrow-n)").Select line I can't get to work.

I'm trying to identify the last row as lrow. The input number is n. I'm
selecting lrow to lrow minus n , and then deleting.

Hope you can help. Sorry for not being clear before.
 
Hi Don

Thanks for your email.

Sorry if it wasn't clear. I was hoping to enter the number in the input
box and then have it delete this many rows form the end of the active
rows.

For example , I have a worksheet of 300 rows with content. If I enter
200 into the box it would delete the last 200 , leaving the sheet with
the top 100 rows.

I tried it this way , and can't getting it working :

Sub A_Delete_Rows_From_End()

Dim lrow As Long
     With ActiveSheet
         lrow = Range("A" & Rows.Count).End(xlUp).Row
         'Range("A1:A" & lrow).FillDown
     End With

n = Application.InputBox("Delete how many rows from end of file?",
"Input", 0)

If n = False Then Exit Sub

     Rows("(lrow):(lrow-n)").Select
     Selection.Delete Shift:=xlUp

End Sub

It's the Rows("(lrow):(lrow-n)").Select line I can't get to work.

I'm trying to identify the last row as lrow. The input number is n. I'm
selecting lrow to lrow minus n , and then deleting.

Hope you can help. Sorry for not being clear before.
Click to expand...

Still not quite sure about which_______rows you want to delete but
your code cant work
Try this withOUT selections
Rows(lrow).resize(lrow-n).Delete
 
Don said:
Still not quite sure about which_______rows you want to delete but
your code cant work
Try this withOUT selections
Rows(lrow).resize(lrow-n).Delete
Click to expand...

Hi Don

Ok thanks - I'll give it a go.

Grateful for your help.


Best Wishes
 
Colin Hayes said:
Hi Don

Ok thanks - I'll give it a go.

Grateful for your help.


Best Wishes
Click to expand...


Hi Don

OK I've got this working fine now. For me it will be a very handy
utility.

FYI This is the code :

Sub Delete_N_Rows_From_End()
'

Dim lrow As Long
With ActiveSheet
lrow = ActiveSheet.UsedRange.Rows.Count + 1
End With

n = Application.InputBox("Delete how many rows from bottom of sheet?",
"Input Please ...", 0)

If n = False Then Exit Sub

Range(Cells(lrow, 1), Cells(lrow - n, 1)).EntireRow.Delete (up)

'
End Sub



Thanks again.

Best Wishes
 
Hi Don

OK I've got this working fine now. For me it will be a very handy
utility.

FYI This is the code  :

Sub Delete_N_Rows_From_End()
'

Dim lrow As Long
     With ActiveSheet
         lrow = ActiveSheet.UsedRange.Rows.Count + 1
     End With

n = Application.InputBox("Delete how many rows from bottom of sheet?",
"Input Please ...", 0)

If n = False Then Exit Sub

Range(Cells(lrow, 1), Cells(lrow - n, 1)).EntireRow.Delete (up)

'
End Sub

Thanks again.

Best Wishes
Click to expand...
-------------
I think my original did the same thing but, A bit of improvement to
yours

option explicit

Sub Delete_N_Rows_From_End()
Dim lrow As Long
dim n as long

 lrow = UsedRange.Rows.Count + 1
n = Application.InputBox("Delete how many rows from bottom of
sheet?", "Input Please ...", 0) If n = False Then Exit
Sub Range(Cells(lrow, 1), Cells(lrow - n,
1)).EntireRow.Delete 'rows(lr).resize(lrow-n).delete ' works the same
End Sub
 
Don said:
I think my original did the same thing but, A bit of improvement to yours

option explicit

Sub Delete_N_Rows_From_End()
Dim lrow As Long
dim n as long

 lrow = UsedRange.Rows.Count + 1
n = Application.InputBox("Delete how many rows from bottom of sheet?", "Input
Please ...", 0) If n
= False Then Exit Sub Range(Cells(lrow, 1), Cells(lrow - n, 1)).EntireRow.Delete 'ro
ws(lr).resize(lrow
-n).delete ' works the same End Sub
Click to expand...




HI Don

OK thanks for that refinement and for your expertise.



Best Wishes
 
Back
Top