VBA delete rows below last appareance of a string

cazdealer

Board Regular
Joined
Mar 12, 2011
Messages
96
Hi
I would like to put a string in sheet cell A1 of sheet ''info'' and then go to sheet ''data'', find the last appareance of that word in column A and delete all rows below.
if the string isnt there, a box opened saying ''name not found''
How could I do that with VBA?


Also, How I could do the opposite... finding the first appareance of that word and delete all rows above it? (still in column A)
thanks
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Code:
On Error Goto ErrorOutWith Sheet("data").Cells
    Range(.Find(Sheet("Info").Range("A1").Text, After:=.Cells(1,1), searchDirection:=xlPrevious), .Cells(.Rows.Count,.Columns.Count)).EntireRow.Delete
End With
On Error Goto 0
Exit Sub
ErrorOut:
    MsgBox "not found"
    Err.Clear
    On Error Goto 0
Exit Sub
 
Upvote 0
Try

Code:
Sub atest()
Dim LR As Long, Found As Range
With Sheets("Data")
    LR = .Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set Found = .Columns("A").Find(what:=Sheets("info").Range("A1").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Found Is Nothing Then
        MsgBox "Not found", vbInformation
    Else
        .Range("A" & Found.Row + 1 & ":A" & LR).EntireRow.Delete
    End If
End With
End Sub

Edit: for the opposite

Code:
Sub atest()
Dim Found As Range
With Sheets("Data")
    Set Found = .Columns("A").Find(what:=Sheets("info").Range("A1").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Found Is Nothing Then
        MsgBox "Not found", vbInformation
    Else
        .Range("A1:A" & Found.Row - 1).EntireRow.Delete
    End If
End With
End Sub
 
Last edited:
Upvote 0
thanks for the Help guys but both code for removing the rows after the last appearance of the string don't work.

The one Mike provided don't work at all, and the one Peter provided delete all row below the first appearance of that string.

thanks Peter for the second code (to delete rows above), it works.
 
Upvote 0
I missed the last bit. Try

Code:
Sub atest()
Dim LR As Long, i As Long, Found As Range
With Sheets("Data")
    LR = .Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    For i = LR To 1 Step -1
        If .Range("A" & i).Value = Sheets("info").Range("A1").Value Then
            Set Found = .Range("A" & i)
            Exit For
        End If
    Next i
    If Found Is Nothing Then
        MsgBox "Not found", vbInformation
    Else
        .Range("A" & Found.Row + 1 & ":A" & LR).EntireRow.Delete
    End If
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,621
Messages
6,179,936
Members
452,949
Latest member
beartooth91

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