HELP macro to Delete Row based on two cells

randolphoralph · 07-24-2008, 12:25 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

**Glaswegian** · 07-24-2008, 12:49 PM

Hi and welcome to TSF

See if this works for you.

Code:

Sub deletedups()
Dim lRow As Long
Dim i As Long
Dim myRng As Range

Set myRng = Sheets("Sheet1").Range("A1:A5000")

    For lRow = myRng.Rows.Count To 2 Step -1
        If Cells(lRow, 1).Value = 3 Or Cells(lRow, 1).Value = 4 Or Cells(lRow, 1).Value = 5 Then
            If Cells(lRow, 2).Value = "" Then
                Cells(lRow, 1).EntireRow.Delete
            End If
        End If
    Next lRow
End Sub

Adjust sheet names and ranges to suit.

randolphoralph · 07-24-2008, 01:17 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

**Glaswegian** · 07-24-2008, 02:02 PM

Hi

I'm a bit confused here - which way do you want this to work?

Quote:

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

or

Quote:

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.

Also, when you say "date" how is the date formatted? Is it a true date? Is it any date or a specific date?

Please try and be precise - it will help you get a more useful answer.

David M58 · 07-24-2008, 04:20 PM

It appears to me that if column B contains only dates and blank cells, and the original poster wants to delete rows where the column A cell has a value between 3 and 5 and the column B cell is not blank, then if you take this line from the code Glaswegian posted above:

If Cells(lRow, 2).Value = "" Then

and change it to:

If Cells(lRow, 2).Value > "" Then

then it should work.

Now if for some reason you wanted to check that the column B cell really was a date, you might write something like:

If VarType(Cells(lRow, 2).Value) = vbDate Then

07-24-2008, 12:25 PM	#1 (permalink)
randolphoralph Registered User Join Date: Jul 2008 Posts: 4 OS: Windows XP Pro	HELP macro to Delete Row based on two cells 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

07-24-2008, 12:49 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 21,371 OS: Win XP Pro SP3 My System Blog Entries: 10	Re: HELP macro to Delete Row based on two cells Hi and welcome to TSF See if this works for you. Code: Sub deletedups() Dim lRow As Long Dim i As Long Dim myRng As Range Set myRng = Sheets("Sheet1").Range("A1:A5000") For lRow = myRng.Rows.Count To 2 Step -1 If Cells(lRow, 1).Value = 3 Or Cells(lRow, 1).Value = 4 Or Cells(lRow, 1).Value = 5 Then If Cells(lRow, 2).Value = "" Then Cells(lRow, 1).EntireRow.Delete End If End If Next lRow End Sub Adjust sheet names and ranges to suit. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. Ad-Aware::SpywareBlaster::SpyBot::SpywareGuard::SnoopFree::AVG Free::HOSTS File::HijackThis::Donate::5 Steps For Infected PCs

07-24-2008, 01:17 PM	#3 (permalink)
randolphoralph Registered User Join Date: Jul 2008 Posts: 4 OS: Windows XP Pro	Re: HELP macro to Delete Row based on two cells 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 Last edited by randolphoralph : 07-24-2008 at 01:22 PM.

07-24-2008, 04:20 PM	#5 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 97 OS: Windows XP SP3	Re: HELP macro to Delete Row based on two cells It appears to me that if column B contains only dates and blank cells, and the original poster wants to delete rows where the column A cell has a value between 3 and 5 and the column B cell is not blank, then if you take this line from the code Glaswegian posted above: If Cells(lRow, 2).Value = "" Then and change it to: If Cells(lRow, 2).Value > "" Then then it should work. Now if for some reason you wanted to check that the column B cell really was a date, you might write something like: If VarType(Cells(lRow, 2).Value) = vbDate Then