Prev: DTPicker control - Initial date
Next: Getting data from a closed wbook
From: ryguy7272 on 17 May 2010 12:16
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---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
From: Per Jessen on 17 May 2010 12:50
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" <ryguy7272(a)discussions.microsoft.com> skrev i meddelelsen
news:E57A3007-FC21-49E5-AFA3-215BA46E9084(a)microsoft.com...
> 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---
>
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.

From: ryguy7272 on 18 May 2010 11:41
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).EntireRow.Delete
> FilterRng.AutoFilter
> End Sub
>
> Regards,
> Per
>
> "ryguy7272" <ryguy7272(a)discussions.microsoft.com> skrev i meddelelsen
> news:E57A3007-FC21-49E5-AFA3-215BA46E9084(a)microsoft.com...
> > 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---
> >
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
>
> .
>
 | 
Pages: 1
Prev: DTPicker control - Initial date
Next: Getting data from a closed wbook