Macro to Delete the last N rows

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

 

[Don Guillett]

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

 

[Colin Hayes]

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

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.

 

[Don Guillett]

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.

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.

 

[Colin Hayes]

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.

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.

 

[Don Guillett]

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.

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

 

[Colin Hayes]

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

Hi Don

Ok thanks - I'll give it a go.

Grateful for your help.


Best Wishes

 

[Colin Hayes]

Hi Don

Ok thanks - I'll give it a go.

Grateful for your help.


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

 

[Don Guillett]

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

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

 

[Colin Hayes]

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

HI Don

OK thanks for that refinement and for your expertise.



Best Wishes