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