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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.