PDA

View Full Version : Deleting row when a condition is met



surya prakash
08-28-2007, 03:54 AM
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
08-28-2007, 04:11 AM
The following code seems to be running endlessly;
I have about 9000lines.
Can someone have a look please...

thanks

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

Bob Phillips
08-28-2007, 04:23 AM
See if this is quicker



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

surya prakash
08-28-2007, 04:32 AM
Hi XLD,
Many thanks; the macro runs much faster...

surya prakash
08-28-2007, 04:43 AM
Hi XLD,

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

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

.Cells(i + 1, "A").Value = .Cells(i, "B").Value & " " & .Cells(i, "C").Value

Bob Phillips
08-28-2007, 04:50 AM
No, you need to do each, either coded inline or in a loop.

surya prakash
08-28-2007, 06:15 AM
hmm, sorry xld; how do I do that coding.
I am manually adding


.Cells(i + 1, "A").Value = .Cells(i, "B").Value & " " & .Cells(i, "C").Value ......

Bob Phillips
08-28-2007, 06:18 AM
Yes, that or



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