PDA

View Full Version : HELP macro to Delete Row based on two cells



randolphoral
07-24-2008, 12:30 PM
I am trying to write some code that will compare two cells on a row and if they match then it will delete the row.

Column A-has the number 1, 2, 3, 4, or 5 entered
Column B-has either a date or is blank

I need the macro to look at Column A and Column B and for example if the cell A2 has a 3, 4, or 5 and B2 is blank the macro will delete that row.
The spreadsheet will vary on how many rows it contains. It can contain up to 5,000 rows and the macro would need to go through all the rows.
Any help would be greatly appreciated. Thanks

georgiboy
07-24-2008, 12:44 PM
You could try something like this

Sub Deleter()
Dim MyRange As Range, rCell As Range
Dim Stopper As Integer


Stopper = WorksheetFunction.CountA(Range("A:A"))

Set MyRange = Range("A1:A" & Stopper)

For Each rCell In MyRange.Cells

If rCell.Value > 2 Then If rCell.Offset(, 1).Value = "" Then rCell.EntireRow.Delete xlUp

Next rCell


End Sub

hope this helps

randolphoral
07-24-2008, 01:08 PM
I tried using this code and it does not appear to work. Any other thoughts?

Bob Phillips
07-24-2008, 01:16 PM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "A").Value >= 2 And .Cells(i, "B").Value = "" Then

.Rows(i).delee
End If
Next i

End With

Application.ScreenUpdating = True

End Sub

randolphoral
07-24-2008, 01:20 PM
Well I figured out why the previous to code examples were not working I have included a correction as to what I am needing.

I am trying to write some code that will compare two cells on a row and if they match then it will delete the row.

Column A-has the number 1, 2, 3, 4, or 5 entered
Column B-has either a date or is blank

I need the macro to look at Column A and Column B and for example if the cell A2 has a 3, 4, or 5 and B2 has a date the macro will delete that row.
The spreadsheet will vary on how many rows it contains. It can contain up to 5,000 rows and the macro would need to go through all the rows.
Any help would be greatly appreciated. Thanks

Bob Phillips
07-24-2008, 01:25 PM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "A").Value >= 2 And IsDate(.Cells(i, "B").Value) Then

.Rows(i).delee
End If
Next i

End With

Application.ScreenUpdating = True

End Sub