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

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''.

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''.

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

Thanks Per!!!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Per Jessen" wrote:

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''.

.