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

norts55

Board Regular
Joined
Jul 27, 2012
Messages
191
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:p></o:p>
So the short version of the macro I need…<o:p></o:p>
Delete all rows between the last hyperlink in column D tothe text value of “Estimated Bid Price” in column F.<o:p></o:p>
If anyone could help that would be greatly appreciated.<o:p></o:p>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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

Code:
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
 
Upvote 0
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.

 
Upvote 0
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.
 
Upvote 0
.. however, probably don't need to replicate the data for this. Test in a copy of your workbook.
Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,289
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top