Object comes back null and errors...how to fix?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

axwack@gmail.com - 03 Jun 2007 16:20 GMT

Folks,

The following codes looks through a selection and any time the macro
finds the value ("ticker") in the row, it selects that column and then
deletes it.

The following code gets me what I want but the issue is when there is
no value left in the selection, the macro errors saying there is no
Object defined.

I think I declared this incorrectly. Can anyone suggest and
alternative to this?
Sub deleteTickerColumn()

Dim wks As Worksheet
Dim Rng As Range

Set wks = Worksheets("Instructions")
Application.ScreenUpdating = False

With wks
For Each Rng In .Range("h5").CurrentRegion

.Cells.Find(What:="ticker", After:=ActiveCell,
LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Rng Is Nothing Then
Exit Sub
Else
ActiveCell.EntireColumn.Select
Selection.Delete Shift:=xlToLeft
End If

Next Rng
End With
Application.ScreenUpdating = True

End Sub

Reply to this Message

Dave Peterson - 03 Jun 2007 17:13 GMT

Since you're using .find, you won't need to loop through all the cells in that
current region

How about something like:

Option Explicit
Sub deleteTickerColumn()

Dim wks As Worksheet
Dim FoundCell As Range

Set wks = Worksheets("Instructions")
Application.ScreenUpdating = False

With wks
With .Cells 'do you really want: With .Range("h5").CurrentRegion
Do
Set FoundCell = .Cells.Find(What:="ticker", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireColumn.Delete
End If
Loop
End With
End With
Application.ScreenUpdating = True

End Sub

> Folks,
>
[quoted text clipped - 36 lines]
>
> End Sub

Signature

Dave Peterson

Reply to this Message

axwack@gmail.com - 03 Jun 2007 17:30 GMT

> Since you're using .find, you won't need to loop through all the cells in that
> current region
[quoted text clipped - 79 lines]
>
> Dave Peterson

Thanks Dave..let me try this.

Reply to this Message

axwack@gmail.com - 03 Jun 2007 17:32 GMT

> Since you're using .find, you won't need to loop through all the cells in that
> current region
[quoted text clipped - 79 lines]
>
> Dave Peterson

Dave...I just tried it works but how do I end the loop? Will this loop
until the very last column in the active worksheet or will it look for
data in the last column?

Reply to this Message

Dave Peterson - 03 Jun 2007 17:45 GMT

It'll look until it can't find anymore cells with ticker in them.

If it doesn't find one, then it exits the do/loop.

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireColumn.Delete
End If

ps. You may want to adjust this line:

> > Set FoundCell = .Cells.Find(What:="ticker", _
> > after:=.Cells(.Cells.Count), _
[quoted text clipped - 3 lines]
> > MatchCase:=False, _
> > SearchFormat:=False)

to include
lookat:=xlWhole, _
or
lookat:=xlPart, _

Depending on what you want.

<<snipped>>

> Dave...I just tried it works but how do I end the loop? Will this loop
> until the very last column in the active worksheet or will it look for
> data in the last column?

Signature

Dave Peterson

Reply to this Message

Rate this thread: