Deleting row when a condition is met

[surya prakash]

Hi,

I have few cells in different columns (U, X, AA, AD) that consists of either "Check" or "OK".

Is it possible to delete the row, when either of these cells consists of "Check"..

Thanks in advance..

[surya prakash]

The following code seems to be running endlessly;
I have about 9000lines.
Can someone have a look please...

thanks

[VBA]Public Sub DeleteRows()

' Macro to delete rows that has "Check" in one of the columns

Const TEST_COLUMN As String = "X" '<=== change to suit

Dim i As Long
Dim iLastRow As Long

With ActiveSheet

Application.ScreenUpdating = False

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For i = iLastRow To 1 Step -1

If .Cells(i, "U").Value = "Check" Or .Cells(i, "X").Value = "Check" Then
'.Rows(i).Delete
MsgBox "Check Row"
End If
Next i

Application.ScreenUpdating = True

End With

End Sub


[/VBA]

[Bob Phillips]

See if this is quicker

[vba]

Public Sub DeleteRows()
' Macro to delete rows that has "Check" in one of the columns

Const TEST_COLUMN As String = "X" '<=== change to suit

Dim i As Long
Dim iLastRow As Long
Dim rng As Range

With ActiveSheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For i = iLastRow To 1 Step -1

If .Cells(i, "U").Value = "Check" Or .Cells(i, "X").Value = "Check" Then
If rng Is Nothing Then
Set rng = .Rows(i)
Else
Set rng = Union(rng, .Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End With

End Sub
[/vba]

[surya prakash]

Hi XLD,
Many thanks; the macro runs much faster...

[surya prakash]

Hi XLD,

I need to gather text from 8 columns, instead of using the code below
Can I say

.Cells(i, "B:M").Value

[VBA].Cells(i + 1, "A").Value = .Cells(i, "B").Value & " " & .Cells(i, "C").Value[/VBA]

[Bob Phillips]

No, you need to do each, either coded inline or in a loop.

[surya prakash]

hmm, sorry xld; how do I do that coding.
I am manually adding

[VBA]
.Cells(i + 1, "A").Value = .Cells(i, "B").Value & " " & .Cells(i, "C").Value ......
[/VBA]

[Bob Phillips]

Yes, that or

[vba]

For i = 2 to 8
.Cells(i + 1, "A").Value = .Cells(i, "A").Value & " " & .Cells(i, i).Value
Next i
[/vba]