Still stuck. why wont this work?

[crazybuckeyeguy]

I have an excel sheet with multiple rows similar to rows like you see below. the name field is column A and on row 2 the column a is blank. I escentialy need my vba to delete both lines is the second row has MEDL- UNPAID. unfortunalty our stupid progam sometimes puts the MEDL- UNPAID in row 1 and the Multi_skill in row 2. what am I doing wrong?
[TABLE="width: 597"]
<tbody>[TR]
[TD="colspan: 2"]KUBALEK, JAMILA[/TD]
[TD="colspan: 2"]Multi_Skill[/TD]
[TD] 6:00 AM[/TD]
[TD] [/TD]
[TD] 2:30 PM[/TD]
[TD]Multi_Skill[/TD]
[TD] [/TD]
[TD] 6:00 AM[/TD]
[TD] [/TD]
[TD] 2:30 PM[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"]MEDL- UNPAID[/TD]
[TD] 6:00 AM[/TD]
[TD] [/TD]
[TD] 2:30 PM[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col span="2"><col><col><col><col><col></colgroup>[/TABLE]


Sub fixBlanknametest()
Sheets("Data").Activate
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 12 Step -1

If Cells(i & 8).Value = "Multi_skill" And Cells(i & 1 + 1).Value = "" And Cells(i & 8 + 1).Value = "MEDL- UNPAID" _
Then
Rows(i).Delete
End If
Next i
End Sub

 

[Trebor76]

Re: Still stuck. why wont this work??????

Hi there,

Let me know how this goes (just initially try it on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub fixBlanknametest()

    Dim lr As Long
    Dim i  As Long
    
    Application.ScreenUpdating = False

    Sheets("Data").Activate
    lr = Cells(Rows.Count, "A").End(xlUp).Row 'Gets the last row from Col. A.  Is this reliable??
    For i = lr To 12 Step -1
        If StrConv(Cells(i, "H"), vbLowerCase) = "multi_skill" And Len(Cells(i, "B")) = 0 And StrConv(Cells(i, "H"), vbLowerCase) = "medl- unpaid" Then
            Rows(i).Delete
        End If
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[crazybuckeyeguy]

Re: Still stuck. why wont this work??????

Hi there,

Let me know how this goes (just initially try it on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub fixBlanknametest()

    Dim lr As Long
    Dim i  As Long
    
    Application.ScreenUpdating = False

    Sheets("Data").Activate
    lr = Cells(Rows.Count, "A").End(xlUp).Row 'Gets the last row from Col. A.  Is this reliable??
    For i = lr To 12 Step -1
        If StrConv(Cells(i, "H"), vbLowerCase) = "multi_skill" And Len(Cells(i, "B")) = 0 And StrConv(Cells(i, "H"), vbLowerCase) = "medl- unpaid" Then
            Rows(i).Delete
        End If
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

thanks, it did not work. I attached a link of my excel worksheet hope this helps. https://ibb.co/d3kyJk

 

[Trebor76]

Re: Still stuck. why wont this work??????

It's very hard to know what's happening from a picture but there seems to a space on either side of the dash in "MEDL - UNPAID" where your original code (and hence my code) only had one space i.e. "MEDL- UNPAID".

Add the extra space so the line looks like this...

If StrConv(Cells(i, "H"), vbLowerCase) = "multi_skill" And Len(Cells(i, "B")) = 0 And StrConv(Cells(i, "H"), vbLowerCase) = "medl - unpaid" Then

...and try again.

Robert

 

[crazybuckeyeguy]

Re: Still stuck. why wont this work??????

It's very hard to know what's happening from a picture but there seems to a space on either side of the dash in "MEDL - UNPAID" where your original code (and hence my code) only had one space i.e. "MEDL- UNPAID".

Add the extra space so the line looks like this...

If StrConv(Cells(i, "H"), vbLowerCase) = "multi_skill" And Len(Cells(i, "B")) = 0 And StrConv(Cells(i, "H"), vbLowerCase) = "medl - unpaid" Then

...and try again.

Robert

Yea it is hard to see it is MEDL- UNPAID. I tried changing it before replying earlier.

 

[Trebor76]

Re: Still stuck. why wont this work??????

The other thing is that "Multi_Skill" is in Col. H in the row immediately above where the code is at which I overlooked so see how this goes:

If StrConv(Cells(i-1, "H"), vbLowerCase) = "multi_skill" And Len(Cells(i, "B")) = 0 And StrConv(Cells(i, "H"), vbLowerCase) = "medl - unpaid" Then

Regards,

Robert