Question: I have code like below because I would like to delete all rows which contain "Something" value but when I start it, it does not delete every row just some, I have click 5 times to delete every row, how to implement macro which will be deleted all rows which contain "Something" by one click ? Thanks in advance

```
Dim cell As Range

Worksheets("Something").Activate

For Each cell In Cells.SpecialCells(xlTextValues)
If cell = "Something" Then
cell.EntireRow.Delete
End If

Next cell

```

Answer: 
If all the values are in column A, say, then:

```
Worksheets("Something").Activate

With ActiveSheet
    Dim LastRow as Long
    LastRow = .Cells(.Rows.Count,1).end(xlUp).Row
    Dim i As Long

    For i = LastRow to 1 Step -1
        If .Cells(i,1).Value = "Something" Then
            .Rows(i).Delete
        End If
    Next
End With

```
The `xlTextValues` is for the optional *value* parameter of the [Range.SpecialCells method](https://msdn.microsoft.com/en-us/library/office/ff196157.aspx), not the primary *Type* where you have it.

You will need to choose between `xlCellTypeConstants` and `xlCellTypeFormulas` with `xlTextValues` as the option. If you need both, you will have to run it twice.

```
Dim cell As Range

with Worksheets("Something")
    .activate
    For Each cell In Cells.SpecialCells(xlCellTypeFormulas, xlTextValues)
        If cell = "Something" Then
            cell.EntireRow.Delete
        End If
    Next cell
    For Each cell In Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
        If cell = "Something" Then
            cell.EntireRow.Delete
        End If
    Next cell
end with

```

The `xlCellTypeConstants` and `xlTextValues` both share a numerical value of 2 (although used for different purposes). For all intents and purposes, you were looking through the `xlCellTypeConstants` for any type of value, including errors.

VBA's default comparison operator is `vbBinaryCompare` which is case sensitive. Use something like `If Lcase(cell) = "something" Then` for a non-case sensitive comparison.