Test variable range for 'Delete'; then delete the row
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).EntireRo w.Delete
FilterRng.AutoFilter
End Sub
Regards,
Per
"ryguy7272" skrev i meddelelsen
...
Im getting a run-time error 1004 here. Application defined or object
defined error.
The error seems to occur he
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 wont 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---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
|