Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
I am working on More macros and I am trying to figure out how to Select Every
row except for the last 10. SO: Select rows 1 through Last Row - 9. Delete selected rows LastRow = Cells(Rows.Count, "V").End(xlUp).Row Cells(LastRow + 2, "V") = Application.Sum _ (Range(Cells(1, "V"), Cells(LastRow, "V"))) That is what I am using in another macro for selecting the last row +2 in Column V, then summing the entire column within it. NOW, I figured I could take that and pull a last row -9 from it and having it delete the entire row, but I'm having some poor luck. I've managed to get it to delete everything within the Column, but I can't seem to figure out how to make it get rid of the rows. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
1. make a range with cells in each row you want to delete and then use
Entire.Row Sub Macro1() Dim r As Range Set r = Range("A1:A9") r.EntireRow.Delete End Sub -- Gary''s Student "bodhisatvaofboogie" wrote: I am working on More macros and I am trying to figure out how to Select Every row except for the last 10. SO: Select rows 1 through Last Row - 9. Delete selected rows LastRow = Cells(Rows.Count, "V").End(xlUp).Row Cells(LastRow + 2, "V") = Application.Sum _ (Range(Cells(1, "V"), Cells(LastRow, "V"))) That is what I am using in another macro for selecting the last row +2 in Column V, then summing the entire column within it. NOW, I figured I could take that and pull a last row -9 from it and having it delete the entire row, but I'm having some poor luck. I've managed to get it to delete everything within the Column, but I can't seem to figure out how to make it get rid of the rows. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
Well the range of rows is not always going to be the same. Entering a set
range will make all future imported data not format properly. I'll take that and see if I can add the LastRow function into a range. That is what I haven't figured out yet. :) That way regardless of how many rows pop into the document, it will be formated based on the last row -9 and effectively delete what I want gone. Thanks for the input. "Gary''s Student" wrote: 1. make a range with cells in each row you want to delete and then use Entire.Row Sub Macro1() Dim r As Range Set r = Range("A1:A9") r.EntireRow.Delete End Sub -- Gary''s Student "bodhisatvaofboogie" wrote: I am working on More macros and I am trying to figure out how to Select Every row except for the last 10. SO: Select rows 1 through Last Row - 9. Delete selected rows LastRow = Cells(Rows.Count, "V").End(xlUp).Row Cells(LastRow + 2, "V") = Application.Sum _ (Range(Cells(1, "V"), Cells(LastRow, "V"))) That is what I am using in another macro for selecting the last row +2 in Column V, then summing the entire column within it. NOW, I figured I could take that and pull a last row -9 from it and having it delete the entire row, but I'm having some poor luck. I've managed to get it to delete everything within the Column, but I can't seem to figure out how to make it get rid of the rows. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
To leave 10 rows:
Sub dsfe() Dim r As Range Dim lastrow As Long lastrow = Cells(Rows.Count, "V").End(xlUp).Row Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "v")) r.EntireRow.Delete End Sub -- Gary''s Student "bodhisatvaofboogie" wrote: Well the range of rows is not always going to be the same. Entering a set range will make all future imported data not format properly. I'll take that and see if I can add the LastRow function into a range. That is what I haven't figured out yet. :) That way regardless of how many rows pop into the document, it will be formated based on the last row -9 and effectively delete what I want gone. Thanks for the input. "Gary''s Student" wrote: 1. make a range with cells in each row you want to delete and then use Entire.Row Sub Macro1() Dim r As Range Set r = Range("A1:A9") r.EntireRow.Delete End Sub -- Gary''s Student "bodhisatvaofboogie" wrote: I am working on More macros and I am trying to figure out how to Select Every row except for the last 10. SO: Select rows 1 through Last Row - 9. Delete selected rows LastRow = Cells(Rows.Count, "V").End(xlUp).Row Cells(LastRow + 2, "V") = Application.Sum _ (Range(Cells(1, "V"), Cells(LastRow, "V"))) That is what I am using in another macro for selecting the last row +2 in Column V, then summing the entire column within it. NOW, I figured I could take that and pull a last row -9 from it and having it delete the entire row, but I'm having some poor luck. I've managed to get it to delete everything within the Column, but I can't seem to figure out how to make it get rid of the rows. Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
If you want to leave only the last row:
Sub dsfe() Dim r As Range Dim lastrow As Long lastrow = Cells(Rows.Count, "V").End(xlUp).Row Set r = Range(Cells(1, "V"), Cells(lastrow - 1, "v")) r.EntireRow.Delete End Sub -- Gary''s Student "bodhisatvaofboogie" wrote: I am working on More macros and I am trying to figure out how to Select Every row except for the last 10. SO: Select rows 1 through Last Row - 9. Delete selected rows LastRow = Cells(Rows.Count, "V").End(xlUp).Row Cells(LastRow + 2, "V") = Application.Sum _ (Range(Cells(1, "V"), Cells(LastRow, "V"))) That is what I am using in another macro for selecting the last row +2 in Column V, then summing the entire column within it. NOW, I figured I could take that and pull a last row -9 from it and having it delete the entire row, but I'm having some poor luck. I've managed to get it to delete everything within the Column, but I can't seem to figure out how to make it get rid of the rows. Any ideas? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
It is stopping me at this line:
Set r = Range(Cells(1, "V"), Cells(lastrow - 1, "v")) I've been trying variations of that line myself, and had no luck. What am I missin? "Gary''s Student" wrote: If you want to leave only the last row: Sub dsfe() Dim r As Range Dim lastrow As Long lastrow = Cells(Rows.Count, "V").End(xlUp).Row Set r = Range(Cells(1, "V"), Cells(lastrow - 1, "v")) r.EntireRow.Delete End Sub -- Gary''s Student "bodhisatvaofboogie" wrote: I am working on More macros and I am trying to figure out how to Select Every row except for the last 10. SO: Select rows 1 through Last Row - 9. Delete selected rows LastRow = Cells(Rows.Count, "V").End(xlUp).Row Cells(LastRow + 2, "V") = Application.Sum _ (Range(Cells(1, "V"), Cells(LastRow, "V"))) That is what I am using in another macro for selecting the last row +2 in Column V, then summing the entire column within it. NOW, I figured I could take that and pull a last row -9 from it and having it delete the entire row, but I'm having some poor luck. I've managed to get it to delete everything within the Column, but I can't seem to figure out how to make it get rid of the rows. Any ideas? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
Let's MSGBOX(lastrow) right before the Set statement.
Make sure its not 0 for any reason. -- Gary''s Student "bodhisatvaofboogie" wrote: It is stopping me at this line: Set r = Range(Cells(1, "V"), Cells(lastrow - 1, "v")) I've been trying variations of that line myself, and had no luck. What am I missin? "Gary''s Student" wrote: If you want to leave only the last row: Sub dsfe() Dim r As Range Dim lastrow As Long lastrow = Cells(Rows.Count, "V").End(xlUp).Row Set r = Range(Cells(1, "V"), Cells(lastrow - 1, "v")) r.EntireRow.Delete End Sub -- Gary''s Student "bodhisatvaofboogie" wrote: I am working on More macros and I am trying to figure out how to Select Every row except for the last 10. SO: Select rows 1 through Last Row - 9. Delete selected rows LastRow = Cells(Rows.Count, "V").End(xlUp).Row Cells(LastRow + 2, "V") = Application.Sum _ (Range(Cells(1, "V"), Cells(LastRow, "V"))) That is what I am using in another macro for selecting the last row +2 in Column V, then summing the entire column within it. NOW, I figured I could take that and pull a last row -9 from it and having it delete the entire row, but I'm having some poor luck. I've managed to get it to delete everything within the Column, but I can't seem to figure out how to make it get rid of the rows. Any ideas? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
I'm not sure I understand what you mean.
"Gary''s Student" wrote: Let's MSGBOX(lastrow) right before the Set statement. Make sure its not 0 for any reason. -- Gary''s Student "bodhisatvaofboogie" wrote: It is stopping me at this line: Set r = Range(Cells(1, "V"), Cells(lastrow - 1, "v")) I've been trying variations of that line myself, and had no luck. What am I missin? "Gary''s Student" wrote: If you want to leave only the last row: Sub dsfe() Dim r As Range Dim lastrow As Long lastrow = Cells(Rows.Count, "V").End(xlUp).Row Set r = Range(Cells(1, "V"), Cells(lastrow - 1, "v")) r.EntireRow.Delete End Sub -- Gary''s Student "bodhisatvaofboogie" wrote: I am working on More macros and I am trying to figure out how to Select Every row except for the last 10. SO: Select rows 1 through Last Row - 9. Delete selected rows LastRow = Cells(Rows.Count, "V").End(xlUp).Row Cells(LastRow + 2, "V") = Application.Sum _ (Range(Cells(1, "V"), Cells(LastRow, "V"))) That is what I am using in another macro for selecting the last row +2 in Column V, then summing the entire column within it. NOW, I figured I could take that and pull a last row -9 from it and having it delete the entire row, but I'm having some poor luck. I've managed to get it to delete everything within the Column, but I can't seem to figure out how to make it get rid of the rows. Any ideas? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
Because I can't see what you have in column V, I can't determine why the code
stops. In this form: Sub dsfe() Dim r As Range Dim lastrow As Long lastrow = Cells(Rows.Count, "V").End(xlUp).Row MsgBox (lastrow) Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V")) r.EntireRow.Delete End Sub the code will not delete the last 10 lines, but lastrow must be 11 or greater. -- Gary's Student "bodhisatvaofboogie" wrote: I'm not sure I understand what you mean. "Gary''s Student" wrote: Let's MSGBOX(lastrow) right before the Set statement. Make sure its not 0 for any reason. -- Gary''s Student "bodhisatvaofboogie" wrote: It is stopping me at this line: Set r = Range(Cells(1, "V"), Cells(lastrow - 1, "v")) I've been trying variations of that line myself, and had no luck. What am I missin? "Gary''s Student" wrote: If you want to leave only the last row: Sub dsfe() Dim r As Range Dim lastrow As Long lastrow = Cells(Rows.Count, "V").End(xlUp).Row Set r = Range(Cells(1, "V"), Cells(lastrow - 1, "v")) r.EntireRow.Delete End Sub -- Gary''s Student "bodhisatvaofboogie" wrote: I am working on More macros and I am trying to figure out how to Select Every row except for the last 10. SO: Select rows 1 through Last Row - 9. Delete selected rows LastRow = Cells(Rows.Count, "V").End(xlUp).Row Cells(LastRow + 2, "V") = Application.Sum _ (Range(Cells(1, "V"), Cells(LastRow, "V"))) That is what I am using in another macro for selecting the last row +2 in Column V, then summing the entire column within it. NOW, I figured I could take that and pull a last row -9 from it and having it delete the entire row, but I'm having some poor luck. I've managed to get it to delete everything within the Column, but I can't seem to figure out how to make it get rid of the rows. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting cells and shifting "right" | Excel Discussion (Misc queries) | |||
Deleting contents of cells in non contiguous ranges | Excel Worksheet Functions | |||
Deleting a column | Excel Discussion (Misc queries) | |||
Scrolls Far Below Actual Data | Excel Discussion (Misc queries) | |||
How to delete duplicate records when I merge two lists (deleting . | Excel Worksheet Functions |