Macro to delete all rows between two other rows with certain criteria

[norts55]

I transfer information from one worksheet to another. I put the transferred info at the top of thepage in any number of rows. At thebottom of the page I have a couple of rows I use to sum some of thecolumns. What I need is a macro thatwill delete all rows from the last line that I put in the top to the first linethat I have at the bottom. What I amthinking I could use for criteria is in column D there are hyperlinks and incolumn F there is a text value of “Estimated Bid Price” (without the quotes). <o></o>
So the short version of the macro I need…<o></o>
Delete all rows between the last hyperlink in column D tothe text value of “Estimated Bid Price” in column F.<o></o>
If anyone could help that would be greatly appreciated.<o></o>

 

[Momentman]

Not sure how the data is structured.

I assumed that the bottom row in D is lower than the Estimated Bid price in column F

Sub newdata()
    lastrow = Range("F65536").End(xlUp).Row
    For I = 1 To lastrow
        If Range("D" & I).Hyperlinks.Count = 0 Then
            Exit For
        End If
    Next I

For j = I + 1 To lastrow Step 1
        If Range("F" & j).Value = "Estimated Bid Price" Then
            Exit For
        End If
    Next j
    
    'MsgBox I & " " & j
    Range(Cells(I, 1), Cells(j, 1)).EntireRow.Delete
End Sub

 

[norts55]

Thank you so much for the response. The macro you show deleted too many rows. It deleted everything but the last rows. I have an image that I hope will help to show the criteria that I am using to delete the rows.

 

[norts55]

[/URL] Uploaded with ImageShack.us[/IMG]

 

[Peter_SSs]

Thank you so much for the response. The macro you show deleted too many rows. It deleted everything but the last rows. I have an image that I hope will help to show the criteria that I am using to delete the rows.

You cannot display an image stored on your own computer.
My signature block suggests several ways to show small screen shots and they have the advantage that a helper can copy the data to their own sheet to test.
Another, not do useful, way is to upload your image to a file-sharing site and provide a link to that image. Disadvantage is the inability to copy, thereby reducing the number of potential helpers.

Edit: Ah, I see you got to using the file-share site - but there's a lot of typing if somebody wants to replicate your data.

 

[Peter_SSs]

.. however, probably don't need to replicate the data for this. Test in a copy of your workbook.

Sub Del_Rows()
  Dim lrD As Long, lrH As Long
  
  lrD = Range("D" & Rows.Count).End(xlUp).Row
  lrH = Range("H" & Rows.Count).End(xlUp).Row
  If lrH > lrD + 1 Then
    Rows(lrD + 1 & ":" & lrH - 1).Delete
  End If
End Sub

 

[norts55]

Wow! Thank you that worked perfectly. But I have one more request. Is there a way to modify it so I will start the delete 5 rows below the last hyperlink? So essentially there will be 5 blank rows that I am able to add information manually if needed. I am sorry I did not explain that in the initial request.

 

[Peter_SSs]

Is there a way to modify it so I will start the delete 5 rows below the last hyperlink? So essentially there will be 5 blank rows that I am able to add information manually if needed.

Sure, leave however many rows you want, just change the "Const" line.

Sub Del_Rows()
  Dim lrD As Long, lrH As Long
  
  Const BlanksToLeave As Long = 5 '<- Change this to suit
  
  lrD = Range("D" & Rows.Count).End(xlUp).Row
  lrH = Range("H" & Rows.Count).End(xlUp).Row
  If lrH > lrD + BlanksToLeave + 1 Then
    Rows(lrD + BlanksToLeave + 1 & ":" & lrH - 1).Delete
  End If
End Sub

 

[norts55]

Thank you very much. It works great!