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
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? |
#5
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? |
#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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
The message box came up as a 1 then it stopped the macro and went into
debug. So what next? "Gary''s Student" wrote: 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? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
The message is telling us that Excel thinks that there is only one row
available (that only V1 has data and there is nothing else in the column). This means that there are no rows that can be deleted! For example, if lastrow was 11 then the macro could delete the first row and still leave 10 rows. If lastrow was 12 then the macro could delete the first two rows, etc. In other words, rows can only be deleted if there are more than 10. Here is a version of the macro that will delete extra rows if it can and not go into error mode: Sub dsfe() Dim r As Range Dim lastrow As Long lastrow = Cells(Rows.Count, "V").End(xlUp).Row If lastrow < 11 Then Exit Sub Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V")) r.EntireRow.Delete End Sub -- Gary''s Student "bodhisatvaofboogie" wrote: The message box came up as a 1 then it stopped the macro and went into debug. So what next? "Gary''s Student" wrote: 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? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting AND Deleting
That worked great, THANKS!!! got it figured out now and working well. :)
"Gary''s Student" wrote: The message is telling us that Excel thinks that there is only one row available (that only V1 has data and there is nothing else in the column). This means that there are no rows that can be deleted! For example, if lastrow was 11 then the macro could delete the first row and still leave 10 rows. If lastrow was 12 then the macro could delete the first two rows, etc. In other words, rows can only be deleted if there are more than 10. Here is a version of the macro that will delete extra rows if it can and not go into error mode: Sub dsfe() Dim r As Range Dim lastrow As Long lastrow = Cells(Rows.Count, "V").End(xlUp).Row If lastrow < 11 Then Exit Sub Set r = Range(Cells(1, "V"), Cells(lastrow - 10, "V")) r.EntireRow.Delete End Sub -- Gary''s Student "bodhisatvaofboogie" wrote: The message box came up as a 1 then it stopped the macro and went into debug. So what next? "Gary''s Student" wrote: 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 |