Macro to delete specific cells

[Guest]

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

 

[JE McGimpsey]

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

 

[Guest]

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)

 

[Bill Renaud]

<<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.

 

[Guest]

Thanks Bill for your detailed explanation. I can understand better those
commands now.

Orquidea