Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete specific cells
Hi
I have a range of data from A1 to G70 I want to set a macro that deletes the whole rows that column E is not equal to CNR001. The number of rows will be diferent every time. Thanks in advance Orquidea |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete specific cells
One way:
Dim rCell As Range Dim rDelete As Range For Each rCell In Range("E1:E70") With rCell If Not .Text = "CNR001" Then If rDelete Is Nothing Then Set rDelete = .Cells Else Set rDelete = Union(rDelete, .Cells) End If End If End With Next rCell If Not rDelete Is Nothing Then rDelete.EntireRow.Delete In article , orquidea wrote: Hi I have a range of data from A1 to G70 I want to set a macro that deletes the whole rows that column E is not equal to CNR001. The number of rows will be diferent every time. Thanks in advance Orquidea |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete specific cells
Hi
Thanks for your answer. It worked. I took a 3 days course on macros but this subjet is too complex to understand in 3 days. So, I may ask silly questions but asking for clarification is the only way that I learn. What does it do .Cells? What does the instruction Set do? Thanks in advance Orquidea If rDelete Is Nothing Then Set rDelete = .Cells Else Set rDelete = Union(rDelete, .Cells) "JE McGimpsey" wrote: One way: Dim rCell As Range Dim rDelete As Range For Each rCell In Range("E1:E70") With rCell If Not .Text = "CNR001" Then If rDelete Is Nothing Then Set rDelete = .Cells Else Set rDelete = Union(rDelete, .Cells) End If End If End With Next rCell If Not rDelete Is Nothing Then rDelete.EntireRow.Delete In article , orquidea wrote: Hi I have a range of data from A1 to G70 I want to set a macro that deletes the whole rows that column E is not equal to CNR001. The number of rows will be diferent every time. Thanks in advance Orquidea |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete specific cells
<<What does it do .Cells?
Whenever you see a dot (".") in front of something, it means that it is a continuation of a preceding With statement. In the code given: With rCell If Not .Text = "CNR001" Then If rDelete Is Nothing Then Set rDelete = .Cells Else Set rDelete = Union(rDelete, .Cells) End If End If End With The ".Cells" would be read as: "rCell.Cells", since the line "With rCell" is the previous With statement in effect. See Excel VBA Help on the "With" statement for more info. Using With statements speeds up the execution of the code, because the run-time engine has to actually process each "dot" that it sees in the code to "drill down" to the specified property or method for that object. <<What does the instruction Set do? Set must be used anytime you are initializing an object variable (a Range, Worksheet, Chart, etc.). If you don't use it, then you simply are setting the default property to some value. So for example, the statement: rDelete = rSomeOtherCell would simply set the value of whatever rDelete is currently pointing to (maybe cell "E5") to the value in the cell pointed to by rSomeOtherCell (i.e. "ABC"). This is not what you want in this situation. If you want to cause the object variable rDelete to point to the same cell as rSomeOtherCell, then you must use: Set rDelete = rSomeOtherCell This can be seen if you step through the code and watch the Locals window. Click on the "+" symbol next to the variable rDelete so that the object structure opens up. You will see all of the properties (i.e. Column, Row, Value, Formula, Font, etc.). After executing the above statement, the Row, Column, and Count properties of rDelete and rSomeOtherCell will be equal, indicating that they are both pointing to the same range of cells. -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete specific cells
Thanks Bill for your detailed explanation. I can understand better those
commands now. Orquidea "Bill Renaud" wrote: <<What does it do .Cells? Whenever you see a dot (".") in front of something, it means that it is a continuation of a preceding With statement. In the code given: With rCell If Not .Text = "CNR001" Then If rDelete Is Nothing Then Set rDelete = .Cells Else Set rDelete = Union(rDelete, .Cells) End If End If End With The ".Cells" would be read as: "rCell.Cells", since the line "With rCell" is the previous With statement in effect. See Excel VBA Help on the "With" statement for more info. Using With statements speeds up the execution of the code, because the run-time engine has to actually process each "dot" that it sees in the code to "drill down" to the specified property or method for that object. <<What does the instruction Set do? Set must be used anytime you are initializing an object variable (a Range, Worksheet, Chart, etc.). If you don't use it, then you simply are setting the default property to some value. So for example, the statement: rDelete = rSomeOtherCell would simply set the value of whatever rDelete is currently pointing to (maybe cell "E5") to the value in the cell pointed to by rSomeOtherCell (i.e. "ABC"). This is not what you want in this situation. If you want to cause the object variable rDelete to point to the same cell as rSomeOtherCell, then you must use: Set rDelete = rSomeOtherCell This can be seen if you step through the code and watch the Locals window. Click on the "+" symbol next to the variable rDelete so that the object structure opens up. You will see all of the properties (i.e. Column, Row, Value, Formula, Font, etc.). After executing the above statement, the Row, Column, and Count properties of rDelete and rSomeOtherCell will be equal, indicating that they are both pointing to the same range of cells. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to delete specific values in cells | New Users to Excel | |||
Delete cells containing specific data? | Excel Discussion (Misc queries) | |||
macro to select cells containing specific text and delete all cells but these | Excel Programming | |||
Delete specific cells contents in a row with some locked cells in the same row | Excel Programming | |||
Locate and delete specific cells | Excel Discussion (Misc queries) |