If Function

[crazybuckeyeguy]

I know I am over thinking this. I have a spread sheet that we are adding a function for TR. the report we create is stupid and will list them as such:

[TABLE="width: 448"]
<colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="30" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1097;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;" span="2"> <col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="19" style="width: 14pt; mso-width-source: userset; mso-width-alt: 694;"> <col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="19" style="width: 14pt; mso-width-source: userset; mso-width-alt: 694;"> <col width="41" style="width: 31pt; mso-width-source: userset; mso-width-alt: 1499;"> <tbody>[TR]
[TD="width: 140, bgcolor: transparent, colspan: 2"]BUCKNER, JENNIFER[/TD]
[TD="width: 62, bgcolor: transparent, colspan: 2"]Multi_Skill[/TD]
[TD="width: 69, bgcolor: transparent"] 6:30 AM[/TD]
[TD="width: 71, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"] 3:00 PM[/TD]
[TD="width: 56, bgcolor: transparent"]Multi_Skill[/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 49, bgcolor: transparent"] 6:30 AM[/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 41, bgcolor: transparent"] 3:00 PM[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TR[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 6:30 AM[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 3:00 PM[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]CAREFOOT, CHRISTINA[/TD]
[TD="bgcolor: transparent, colspan: 2"]Multi_Skill[/TD]
[TD="bgcolor: transparent"] 6:30 AM[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 3:00 PM[/TD]
[TD="bgcolor: transparent"]TR[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 6:30 AM[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 3:00 PM[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Multi_Skill[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 6:30 AM[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] 3:00 PM
[/TD]
[/TR]
</tbody>[/TABLE]
I need TR line not the multi_skill line. I obviously cant just delete the multi_skill line. I need this for some other agents. What am I doing wrong in the formula below?

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

If Range("A" & My_ROWS).Value = "" And Range("H" & My_ROWS).Value = "TR" Then _
or Range("H" & My_ROWS).Value = "TR" And Range("A" & My_ROWS + 1).Value = "TR" Then
Range("A" & My_ROWS).Copy Range("A" & My_ROWS + 1) and Rows(my_rows).delete
end if
Range("A" & My_ROWS - 1).Copy Range("A" & My_ROWS) and Rows(My_ROWS - 1).Delete

Next My_ROWS

 

[JPARKHURST]

just a quick check for you, unless I'm mistaken, you need

If Range("A" & My_ROWS).Value = "" And Range("H" & My_ROWS).Value = "TR" Then _
or Range("H" & My_ROWS).Value = "TR" And Range("A" & My_ROWS + 1).Value = "TR" Then

this gone. You may try that, see if you have success

 

[crazybuckeyeguy]

Sorry I did. When I posted it I couldn't type in the box without issues.

 

[JPARKHURST]

OK, i would nest until you get your logic down. In the case above, your column "H" is always goign to have TR in it - and I don't see that in your data. (in fact, I don't really see any column "H" in the data).

SO - I would ask, can you not just check the two version for TR? ColA & ColA+1? Or is ColH on a different sheet?

This code is direct translation from yours, just replacing values with variables for readability.

dim colA as string, colB as string, colH as string

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

 colA = Range("A" & MY_ROWS).Value
 colB = Range("A" & MY_ROWS+1).Value
 colH = Range("H" & MY_ROWS).Value

  If colH ="TR" then
    if colA = "" then
      Range("A" & My_ROWS).Copy Range("A" & My_ROWS + 1) and Rows(my_rows).delete
    elseif colB = "TR"
      Range("A" & My_ROWS).Copy Range("A" & My_ROWS + 1) and Rows(my_rows).delete
    End if
  End If

  Range("A" & My_ROWS - 1).Copy Range("A" & My_ROWS) and Rows(My_ROWS - 1).Delete 

Next My_ROWS

Let me know if that works out a little better for you. If it does, we can un-nest some of these and try with the And/Or. Easiest for me when I hit a wall to break everything into bits and attack it that way.

-In re-reading this, I'm wondering if column A & H are backwards - Should you be checking for H+1 instead?

 

[crazybuckeyeguy]

OK, i would nest until you get your logic down. In the case above, your column "H" is always goign to have TR in it - and I don't see that in your data. (in fact, I don't really see any column "H" in the data).

SO - I would ask, can you not just check the two version for TR? ColA & ColA+1? Or is ColH on a different sheet?

This code is direct translation from yours, just replacing values with variables for readability.

dim colA as string, colB as string, colH as string

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

 colA = Range("A" & MY_ROWS).Value
 colB = Range("A" & MY_ROWS+1).Value
 colH = Range("H" & MY_ROWS).Value

  If colH ="TR" then
    if colA = "" then
      Range("A" & My_ROWS).Copy Range("A" & My_ROWS + 1) and Rows(my_rows).delete
    elseif colB = "TR"
      Range("A" & My_ROWS).Copy Range("A" & My_ROWS + 1) and Rows(my_rows).delete
    End if
  End If

  Range("A" & My_ROWS - 1).Copy Range("A" & My_ROWS) and Rows(My_ROWS - 1).Delete 

Next My_ROWS

Let me know if that works out a little better for you. If it does, we can un-nest some of these and try with the And/Or. Easiest for me when I hit a wall to break everything into bits and attack it that way.

-In re-reading this, I'm wondering if column A & H are backwards - Should you be checking for H+1 instead?

thx, i just posted a simplified version so you could see what the data I am looking for is. TR is not always there they vary. therefore I need something that will look for a TR and duplicate names then delete the row that doesn't have the TR.