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

 

[Ron Rosenfeld]

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.

Here's another macro approach.

One of the problems with using UsedRange to find the last row is that if the last row has previously been cleared, it may still show up in UsedRange unless UsedRange has been reset. So I use UsedRange as a starting point, but not as the final determinant.

==========================
Option Explicit
Sub DeleteN_Rows()
Dim lNumRows As Long
Dim lLastRow As Long
Dim i As Long
Dim rg As Range
lNumRows = Application.InputBox("How many rows to delete?", Type:=2)
If lNumRows = 0 Then Exit Sub

With ActiveSheet.UsedRange
If lNumRows >= .Rows.Count Then
.ClearContents
Exit Sub
End If


Set rg = Cells.SpecialCells(xlCellTypeLastCell)
For i = 1 To rg.Column
lLastRow = WorksheetFunction.Max(lLastRow, Cells(Cells.Rows.Count, i).End(xlUp).Row)
Next i

Set rg = Range(Cells(.Row + lLastRow - lNumRows, "A"), Cells(lLastRow, "A")).EntireRow

rg.Delete

End With
End Sub
=================================

 

[Ron Rosenfeld]

One of the problems with using UsedRange to find the last row is that if the last row has previously been cleared, it may still show up in UsedRange unless UsedRange has been reset. So I use UsedRange as a starting point, but not as the final determinant.

It may be that accessing UsedRange in VBA always resets it properly, according to some brief Googling without extensive testing.
So that objection of mine may be moot. If that is the case, then the previous, shorter routines would be better.