Stumped and looking for solution tonight,

[crazybuckeyeguy]

OK so here is the VBA I am using. its supposed to delete the row with the work Multi_Skill if the row below it has a blank cell in column A and a specific text in column H. the dang thing isn't working. What have I done wrong?

Sub fixBlankname()
Sheets("Data").Activate
For My_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "FMLA" _
Or Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "FMLVAC" _
Or Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "GRPVAC" _
Or Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "FS" _
Or Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "PO" _
Or Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "vaca" _
Or Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "FMLAPO" _
Or Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "vaca" _
Or Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "MEDL- UNPAID" _
Or Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "MEDL- PAID" _
Or Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "SK" _
Or Range("H" & My_ROWS).Value = "Multi_skill" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "DF" Then
Rows(My_ROWS).Delete
End If

If Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "FMLA" _
Or Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "FMLVAC" _
Or Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "GRPVAC" _
Or Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "PO" _
Or Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "SK" _
Or Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "DF" _
Or Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "FS" _
Or Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "vaca" _
Or Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "FMLAPO" _
Or Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "vaca" _
Or Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "MEDL- UNPAID" _
Or Range("H" & My_ROWS).Value = "SEALEA" And Range("A" & My_ROWS + 1).Value = "" And Range("H" & My_ROWS + 1).Value = "MEDL- PAID" Then


Rows(My_ROWS).Delete
End If
If Range("H" & My_ROWS).Value = "VACA" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "FMLA" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "FMLAPO" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "FMLA-MEDR" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "FMLAPO" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "FS" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "FSCU" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "GRPVAC" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "HOLVAC" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "PO" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "FMLVAC" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "MEDL- UNPAID" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "INOFCE" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "MEDL- PAID" And Range("A" & My_ROWS + 1).Value = "" _
Or Range("H" & My_ROWS).Value = "SK" And Range("A" & My_ROWS + 1).Value = "" Then
Rows(My_ROWS + 1).Delete
End If

Next My_ROWS
End Sub

 

[footoo]

Although it would be advisable to re-write your code to make it more efficient, as a quick fix change to :

For My_ROWS =Range("A" & Rows.Count).End(xlUp).Row to 1 Step -1

And do three separate loops.

 

[crazybuckeyeguy]

Although it would be advisable to re-write your code to make it more efficient, as a quick fix change to :

For My_ROWS =Range("A" & Rows.Count).End(xlUp).Row to 1 Step -1

And do three separate loops.

Any chance you would be willing to help with making it more efficient? This is was about my limitations of VBA code.

 

[footoo]

Any chance you would be willing to help with making it more efficient? This is was about my limitations of VBA code.

No time right now.

See if it works with the change I suggested.
Instead of 3 separate loops you could do :
If
ElseIf
ElseIf
End If

 

[crazybuckeyeguy]

Although it would be advisable to re-write your code to make it more efficient, as a quick fix change to :

For My_ROWS =Range("A" & Rows.Count).End(xlUp).Row to 1 Step -1

And do three separate loops.

Still didn't delete out the row like its supposed too.

 

[footoo]

Still didn't delete out the row like its supposed too.

What is it actually doing?

 

[crazybuckeyeguy]

What is it actually doing?

Its doing nothing. Its supposed to the delete the row with the the Multi_skill in H and then the code later cleans up the row that was below it.

 

[Rick Rothstein]

Its doing nothing. Its supposed to the delete the row with the the Multi_skill in H and then the code later cleans up the row that was below it.

If that is all you want to do for now, I think this code will do it for you...

[table="width: 500"]
[tr]
	[td]Columns("H").Replace "Multi_skill", "#N/A", xlWhole, SearchFormat:=False, ReplaceFormat:=False
Columns("H").SpecialCells(xlConstants, xlErrors).EntireRow.Delete[/td]
[/tr]
[/table]

 

[crazybuckeyeguy]

If that is all you want to do for now, I think this code will do it for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Columns("H").Replace "Multi_skill", "#N/A", xlWhole, SearchFormat:=False, ReplaceFormat:=False
Columns("H").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
[/TD]
[/TR]
</tbody>[/TABLE]

I only want the row to delete contigent upon the value in the row below it let me show you an example.

[TABLE="width: 597"]
<tbody>[TR]
[TD="colspan: 2"]KOSTON, KIMBERLY[/TD]
[TD="colspan: 2"]Multi_Skill[/TD]
[TD] 4:30 AM[/TD]
[TD][/TD]
[TD] 1:30 PM[/TD]
[TD]Multi_Skill[/TD]
[TD][/TD]
[TD] 4:30 AM[/TD]
[TD][/TD]
[TD] 7:00 AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FMLA[/TD]
[TD][/TD]
[TD] 7:00 AM[/TD]
[TD][/TD]
[TD] 1:30 PM[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col span="2"><col><col><col><col><col></colgroup>[/TABLE]

 

[Rick Rothstein]

I only want the row to delete contingent upon the value in the row below it...

And that contingency is....?