Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
Hello:
Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
Why have you started another thread?
"childofthe1980s" wrote: Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
Because, you never can tell on these newsgroups who is available and who is
not. Truthfully, how was I to know that you were still available? In any case, I have this situation whittled down to this last little bit. If I can find anybody who can help me modify these two lines of this last little bit of code, then I would be all set. And, I can pursue other interests that I have neglected for two days because of this. childofthe1980s "Mike H" wrote: Why have you started another thread? "childofthe1980s" wrote: Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
This is a COMPLETELY different question than you asked in your first thread
(posted about and hour-and-a-half earlier). In that first posting, there was only one cell with the word Grand in it... now you are saying each cell has one of two "words" in them... either "Grand" or "#VALUE!". Let's see if you can clarify some things we will need to know for us.. If I understand your situation correctly, you have a worksheet with "real" data on it before you run your main macro, right? Then your macro runs and new rows are added after the "real" data apparently now with either "Grand" or "#VALUE!" appearing in each(?) cell in Column A for these new rows of "fake" data, right? Assuming you answered yes to these questions, then... Can there be a #VALUE! error in any cells within your "real" data? When and how do either of these words get into their cells... by code in your main macro or do they get put there manually by the user? -- Rick (MVP - Excel) "childofthe1980s" wrote in message ... Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
I've given you an answer in my modified code in your other thread and if that
doesn't work a more useful description of what went wrong would be helpful "childofthe1980s" wrote: Because, you never can tell on these newsgroups who is available and who is not. Truthfully, how was I to know that you were still available? In any case, I have this situation whittled down to this last little bit. If I can find anybody who can help me modify these two lines of this last little bit of code, then I would be all set. And, I can pursue other interests that I have neglected for two days because of this. childofthe1980s "Mike H" wrote: Why have you started another thread? "childofthe1980s" wrote: Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
You're taking that, personally.
But, to answer your question, when I used your code I got the same results as if I had never used your code in the first place. So, I found a workaround through autofiltering. And, since the topic is slightly different, I thought that I could start a new thread here and get some help in this newsgroup without being accosted. "Mike H" wrote: I've given you an answer in my modified code in your other thread and if that doesn't work a more useful description of what went wrong would be helpful "childofthe1980s" wrote: Because, you never can tell on these newsgroups who is available and who is not. Truthfully, how was I to know that you were still available? In any case, I have this situation whittled down to this last little bit. If I can find anybody who can help me modify these two lines of this last little bit of code, then I would be all set. And, I can pursue other interests that I have neglected for two days because of this. childofthe1980s "Mike H" wrote: Why have you started another thread? "childofthe1980s" wrote: Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
FORGET IT!
I don't understand these attacks on the newsgroups, Rick (and Mike H). I agree that it's a different posting.....that was my point by starting a new thread. Then, before even getting my...ahem....new posting question answered, references to my old questions are made. Sure this ain't personal? I guess I can't come to this newsgroup to ask any questions or post any new threads without being ganged up on. Must be lack of Christmas cheer, I guess. Oh, well...there are other newsgroups that I can try. Merry Christmas, anyway. childofthe1980s "Rick Rothstein" wrote: This is a COMPLETELY different question than you asked in your first thread (posted about and hour-and-a-half earlier). In that first posting, there was only one cell with the word Grand in it... now you are saying each cell has one of two "words" in them... either "Grand" or "#VALUE!". Let's see if you can clarify some things we will need to know for us.. If I understand your situation correctly, you have a worksheet with "real" data on it before you run your main macro, right? Then your macro runs and new rows are added after the "real" data apparently now with either "Grand" or "#VALUE!" appearing in each(?) cell in Column A for these new rows of "fake" data, right? Assuming you answered yes to these questions, then... Can there be a #VALUE! error in any cells within your "real" data? When and how do either of these words get into their cells... by code in your main macro or do they get put there manually by the user? -- Rick (MVP - Excel) "childofthe1980s" wrote in message ... Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
There are no attacks taking place... the problem is that your question is
not well defined (at least, not to me)... we are just trying to understand the parameters under which you are working in order to give you the best answer we can. Remember, you **know** what you have and where everything is... we here, on the other hand, only know what you tell us... things you may be taking for granted might be necessary for us to know in order to give you a workable answer (the addition of the #VALUE! is a significant addition to the information you gave us in your initial posting). The AutoFilter method you are trying to pursue is probably not the best approach, but that is hard to say without knowing more than you have told us so far. The questions I asked in my last post were serious attempts to find out what you actually are trying to do. With that in mind, I do have one more question to add to the others I asked you in my previous post... can the first cell after your "real" data ever contain #VALUE! or will it always contain "Grand". If you will provide answer to all the questions I have asked, I think we can give you a solution for what you want to do. -- Rick (MVP - Excel) "childofthe1980s" wrote in message ... FORGET IT! I don't understand these attacks on the newsgroups, Rick (and Mike H). I agree that it's a different posting.....that was my point by starting a new thread. Then, before even getting my...ahem....new posting question answered, references to my old questions are made. Sure this ain't personal? I guess I can't come to this newsgroup to ask any questions or post any new threads without being ganged up on. Must be lack of Christmas cheer, I guess. Oh, well...there are other newsgroups that I can try. Merry Christmas, anyway. childofthe1980s "Rick Rothstein" wrote: This is a COMPLETELY different question than you asked in your first thread (posted about and hour-and-a-half earlier). In that first posting, there was only one cell with the word Grand in it... now you are saying each cell has one of two "words" in them... either "Grand" or "#VALUE!". Let's see if you can clarify some things we will need to know for us.. If I understand your situation correctly, you have a worksheet with "real" data on it before you run your main macro, right? Then your macro runs and new rows are added after the "real" data apparently now with either "Grand" or "#VALUE!" appearing in each(?) cell in Column A for these new rows of "fake" data, right? Assuming you answered yes to these questions, then... Can there be a #VALUE! error in any cells within your "real" data? When and how do either of these words get into their cells... by code in your main macro or do they get put there manually by the user? -- Rick (MVP - Excel) "childofthe1980s" wrote in message ... Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
You're taking that, personally.
No I'm not, like everyone else who attempts to provide solutions here I'm trying to help. Notwithstanding the additional information you've provided, I'll ask again,did you try the 'second' version of my code I posted in your other thread? What result did you get? Mike "childofthe1980s" wrote: You're taking that, personally. But, to answer your question, when I used your code I got the same results as if I had never used your code in the first place. So, I found a workaround through autofiltering. And, since the topic is slightly different, I thought that I could start a new thread here and get some help in this newsgroup without being accosted. "Mike H" wrote: I've given you an answer in my modified code in your other thread and if that doesn't work a more useful description of what went wrong would be helpful "childofthe1980s" wrote: Because, you never can tell on these newsgroups who is available and who is not. Truthfully, how was I to know that you were still available? In any case, I have this situation whittled down to this last little bit. If I can find anybody who can help me modify these two lines of this last little bit of code, then I would be all set. And, I can pursue other interests that I have neglected for two days because of this. childofthe1980s "Mike H" wrote: Why have you started another thread? "childofthe1980s" wrote: Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
Fortunately, I figured this out on my own. For your reference, here is the
code: I had to (a) arrow down to the cell A2 before filtering out "Grand" and (b) do Shift-Ctrl-End for both the "Grand" and "#VALUE!" criteria. That way, VBA does not reference specific cells! This is all I needed! Merry Christmas to All and, after having worked on this all day, to All a Goodnight!!!!!!! Selection.AutoFilter Range("A2").Select Selection.AutoFilter Field:=1, Criteria1:="Grand" Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete 'Selection.AutoFilter Field:=1, Criteria1:="Grand" 'Rows("623:623").Select 'Selection.Delete Shift:=xlUp Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 "Rick Rothstein" wrote: There are no attacks taking place... the problem is that your question is not well defined (at least, not to me)... we are just trying to understand the parameters under which you are working in order to give you the best answer we can. Remember, you **know** what you have and where everything is... we here, on the other hand, only know what you tell us... things you may be taking for granted might be necessary for us to know in order to give you a workable answer (the addition of the #VALUE! is a significant addition to the information you gave us in your initial posting). The AutoFilter method you are trying to pursue is probably not the best approach, but that is hard to say without knowing more than you have told us so far. The questions I asked in my last post were serious attempts to find out what you actually are trying to do. With that in mind, I do have one more question to add to the others I asked you in my previous post... can the first cell after your "real" data ever contain #VALUE! or will it always contain "Grand". If you will provide answer to all the questions I have asked, I think we can give you a solution for what you want to do. -- Rick (MVP - Excel) "childofthe1980s" wrote in message ... FORGET IT! I don't understand these attacks on the newsgroups, Rick (and Mike H). I agree that it's a different posting.....that was my point by starting a new thread. Then, before even getting my...ahem....new posting question answered, references to my old questions are made. Sure this ain't personal? I guess I can't come to this newsgroup to ask any questions or post any new threads without being ganged up on. Must be lack of Christmas cheer, I guess. Oh, well...there are other newsgroups that I can try. Merry Christmas, anyway. childofthe1980s "Rick Rothstein" wrote: This is a COMPLETELY different question than you asked in your first thread (posted about and hour-and-a-half earlier). In that first posting, there was only one cell with the word Grand in it... now you are saying each cell has one of two "words" in them... either "Grand" or "#VALUE!". Let's see if you can clarify some things we will need to know for us.. If I understand your situation correctly, you have a worksheet with "real" data on it before you run your main macro, right? Then your macro runs and new rows are added after the "real" data apparently now with either "Grand" or "#VALUE!" appearing in each(?) cell in Column A for these new rows of "fake" data, right? Assuming you answered yes to these questions, then... Can there be a #VALUE! error in any cells within your "real" data? When and how do either of these words get into their cells... by code in your main macro or do they get put there manually by the user? -- Rick (MVP - Excel) "childofthe1980s" wrote in message ... Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit . . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
Fortunately, I figured this out on my own. For your reference, here is the
code: I had to (a) arrow down to the cell A2 before filtering out "Grand" and (b) do Shift-Ctrl-End for both the "Grand" and "#VALUE!" criteria. That way, VBA does not reference specific cells! This is all I needed! Merry Christmas to All and, after having worked on this all day, to All a Goodnight!!!!!!! Selection.AutoFilter Range("A2").Select Selection.AutoFilter Field:=1, Criteria1:="Grand" Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete 'Selection.AutoFilter Field:=1, Criteria1:="Grand" 'Rows("623:623").Select 'Selection.Delete Shift:=xlUp Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 "Mike H" wrote: You're taking that, personally. No I'm not, like everyone else who attempts to provide solutions here I'm trying to help. Notwithstanding the additional information you've provided, I'll ask again,did you try the 'second' version of my code I posted in your other thread? What result did you get? Mike "childofthe1980s" wrote: You're taking that, personally. But, to answer your question, when I used your code I got the same results as if I had never used your code in the first place. So, I found a workaround through autofiltering. And, since the topic is slightly different, I thought that I could start a new thread here and get some help in this newsgroup without being accosted. "Mike H" wrote: I've given you an answer in my modified code in your other thread and if that doesn't work a more useful description of what went wrong would be helpful "childofthe1980s" wrote: Because, you never can tell on these newsgroups who is available and who is not. Truthfully, how was I to know that you were still available? In any case, I have this situation whittled down to this last little bit. If I can find anybody who can help me modify these two lines of this last little bit of code, then I would be all set. And, I can pursue other interests that I have neglected for two days because of this. childofthe1980s "Mike H" wrote: Why have you started another thread? "childofthe1980s" wrote: Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
Fortunately, I figured this out on my own. For your reference, here is the
code: I had to (a) arrow down to the cell A2 before filtering out "Grand" and (b) do Shift-Ctrl-End for both the "Grand" and "#VALUE!" criteria. That way, VBA does not reference specific cells! This is all I needed! Merry Christmas to All and, after having worked on this all day, to All a Goodnight!!!!!!! Selection.AutoFilter Range("A2").Select Selection.AutoFilter Field:=1, Criteria1:="Grand" Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete 'Selection.AutoFilter Field:=1, Criteria1:="Grand" 'Rows("623:623").Select 'Selection.Delete Shift:=xlUp Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 "Charabeuh" wrote: 'Hello ! 'Try This: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''' Sub DeleteRows() Dim LastRow, i LastRow = Range("A" & Rows.Count).End(xlUp).Row For i = LastRow To 1 Step -1 If Application.WorksheetFunction.IsErr(Range("A" & i)) Then Rows(i).Delete ElseIf Range("A" & i) = "Grand" Then Rows(i).Delete Else Exit For End If Next i End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''' Happy Christmas ! "childofthe1980s" a écrit dans le message de groupe de discussion : ... Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code for Excel 2007 to hide rows based on sum of several rows not ina range | Excel Programming | |||
Removing rows based on isDate | Excel Programming | |||
Moving rows with out removing from range | Excel Programming | |||
Finding criteria and removing matching rows (Range issue?) | Excel Programming | |||
Removing 1st four rows from a range | Excel Programming |