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

[axwack]

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

 

[Dave Peterson]

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

 

[axwack]

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

Thanks Dave..let me try this.

 

[axwack]

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

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?

 

[Dave Peterson]

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:

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

Depending on what you want.



: