Test variable range for 'Delete'; then delete the row

[ryguy7272]

I’m getting a run-time error 1004 here. Application defined or object
defined error.

The error seems to occur here:
For Each c In Application.Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("O12:O" & rngFound))

Private Sub CommandButton4_Click()
Dim c As Range
Dim rDelete As Range
Dim rngFound As Range
Dim rngToSearch As Range

With Range("A:A")
Set rngToSearch = Sheets("Worksheet").Columns("A")
Set rngFound = rngToSearch.Find(What:="Enter non-listed privately held
securities or groups of assets by asset class.", _
LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=False)
End With

For Each c In Application.Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("O12:O" & rngFound))
If c.Value = "Delete" Then
If rDelete Is Nothing Then
Set rDelete = c
Else
Set rDelete = Application.Union(rDelete, c)
End If
End If
Next c

If Not rDelete Is Nothing Then
rDelete.EntireRow.Delete
End If
End Sub

Basically, I may have ‘Delete’ anywhere from O12 down to some variable row.
There won’t be any occurrences of ‘Delete’ below this row:
Enter non-listed privately held securities or groups of assets by asset class.

Any thoughts?
As always, thanks!!

Ryan---

 

[Per Jessen]

Hi Ryan

In the line mentioned you miss a '.Row' on the rngFound variable:

For Each c In Application.Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("O12:O" & rngFound.Row))

But I would use a autofilter to find all rows to be deleted. It should be a
bit faster. See below and try:

Private Sub CommandButton4_Click()
Dim rngFound As Range
Dim rngToSearch As Range
Dim FilterRng As Range
Dim DeleteRng As Range

Set rngToSearch = Sheets("Worksheet").Columns("A")
Set rngFound = rngToSearch.Find(What:= _
"Enter non-listed privately held securities or groups of assets by asset
class.", _
LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=False)

LastFilterRw = 50 'rngFound.Row
Set FilterRng = Sheets("Worksheet").Range("O11:O" & LastFilterRw)
Set DeleteRng = Sheets("Worksheet").Range("O12:O" & LastFilterRw)

FilterRng.AutoFilter Field:=1, Criteria1:="Delete"
DeleteRng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
FilterRng.AutoFilter
End Sub

Regards,
Per

 

[ryguy7272]

Thanks Per!!!