Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
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 On Nov 23, 7:52*am, Colin Hayes wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
In article
, Don Guillett writes 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
On Nov 23, 9:13*am, Colin Hayes wrote:
In article , Don Guillett writes 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. 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
In article
, Don Guillett writes 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
On Nov 23, 12:33*pm, Colin Hayes wrote:
In article , Don Guillett writes 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. 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
In article
, Don Guillett writes 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
In article , Colin Hayes
writes In article , Don Guillett writes 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 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
On Nov 23, 7:37*pm, Colin Hayes wrote:
In article , Colin Hayes writes In article , Don Guillett writes 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 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
In article
, Don Guillett writes 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
On Wed, 23 Nov 2011 13:52:12 +0000, Colin Hayes wrote:
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 ================================= |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Delete the last N rows
On Thu, 24 Nov 2011 11:41:42 -0500, Ron Rosenfeld wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete rows | Excel Discussion (Misc queries) | |||
Macro to delete rows if... | Excel Discussion (Misc queries) | |||
Delete rows using a macro | Excel Discussion (Misc queries) | |||
Delete all Rows Macro | Excel Discussion (Misc queries) | |||
Macro to Delete Certain Rows | Excel Discussion (Misc queries) |