Excel VBA & nested ifs

Question

I'm trying to delete a row in Excel 2007 via VBA if it meets two requirements: A a termination date greater than one provided (variable datesToRemoveTerminatedMembersFrom) and also if there is a "D" in column R.

The code I'm using does not give me an error, but does not delete the row either:

For i = lastRow To 2 Step -1
    If Range("R" & i).Value = "D" Then
        If Range("D" & i).Value > datesToRemoveTerminatedMembersFrom Then
            Rows(i).Delete
        End If
    End If
Next i

They both work by themselves, but not if I nest them.

Thanks for your help in advance

it works for me..Have you defined lastRow ?

sam092
– sam092

2013-10-31 03:51:01 +00:00
Commented Oct 31, 2013 at 3:51 — sam092
– sam092, Commented Oct 31, 2013 at 3:51
Hi Sam092 lastRow was defined earlier in the script

Chris
– Chris

2013-10-31 05:01:31 +00:00
Commented Oct 31, 2013 at 5:01 — Chris
– Chris, Commented Oct 31, 2013 at 5:01

Santosh · Accepted Answer · 2013-10-31 04:00:44Z

1

Try below code

For i = lastRow To 2 Step -1
    If UCase(Range("R" & i).Value) = "D" Then
        If CDate(Range("D" & i).Value) > CDate(datesToRemoveTerminatedMembersFrom) Then
            Rows(i).Delete
        End If
    End If
Next

answered Oct 31, 2013 at 4:00

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

I tried this but it deleted everything with a D value in column R.

Santosh Over a year ago

@Chris Add breakpoint at If CDate(Range("D" & i).Value) > CDate(datesToRemoveTerminatedMembersFrom) Then .This will show how the condition is working. Make sure the date are in same format.

Chris · Accepted Answer · 2013-10-31 05:07:15Z

0

I actually don't need this anymore. The person I was setting up the spreadsheet application for requested that I delete each record where the termination date was greater than the filedate.

What she actually meant was replace the termination date with "", not delete the row, doh.

Thanks for your help, the code I used in the end was:

For i = lastRow To 2 Step -1

        If CDate(Range("D" & i).Value) > CDate(datesToRemoveTerminatedMembersFrom) Then
            Range("D" & i) = ""
        End If

Next i

answered Oct 31, 2013 at 5:07

Chris

3571 gold badge4 silver badges14 bronze badges

Collectives™ on Stack Overflow

Excel VBA & nested ifs

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related