VBA Find Value in Column Delete all remaining Rows

[Drrellik]

<code class="vb plain">

' remove top rows down to row containing "Msn #"
    Dim rng As Range
    Dim X   As Long
    Const strstart As String = "Msn #"
    X = Cells(Rows.Count, 1).End(xlUp).Row
    
    Set rng = Cells(1, 1).Resize(X).Find(what:=strstart, LookIn:=xlValues, lookat:=xlWhole)
        
    If Not rng Is Nothing Then
        Cells(1, 1).Resize(rng.Row).EntireRow.delete
        Set rng = Nothing
    End If
    
' Remove rows below "Name" in column B


Insert your code here.... :)

I have searched and cant seem to find the right syntax for deleting down after a value is located in column B I managed to search the forum and get the preceding rows done. Any help would be appreciated.

Don</code>

 

[My Aswer Is This]

Assuming we are looking for the value Andy in column B and we want to delete all the rows below Andy
Modify Andy to your needs.

Try this:

Sub Delete_Rows()
'Modified 3-30-18 3:05 AM EDT
Application.ScreenUpdating = False
Dim SearchString As String
Dim SearchRange As Range
SearchString = "Andy"
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Dim r As Long
Set SearchRange = Range("B1:B" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "   Not found": Exit Sub
r = SearchRange.Row
Rows(r + 1 & ":" & Lastrow).Delete
Application.ScreenUpdating = True
End Sub

 

[Drrellik]

Thank you very Much..

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thank you very Much..

 

[jolivanes]

Or in one go:

Sub Maybe()
    Range("A1:A" & Columns(1).Find("Msn #", , , 1).Offset(-1).Row).EntireRow.Delete
    Range("A" & Columns(1).Find("Name", , , 1).Offset(1).Row & ":A" & Cells(Rows.Count, 1).End(xlUp).Row).EntireRow.Delete
End Sub

 

[Drrellik]

Thank you I will try both ways, the Marco is a group of 5 or 6 things I want done to the sheet before printing and I am sure my piece meal approach to coding makes it run slow.

Thanks ~DR