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

  • Thread starter Thread starter axwack
  • Start date Start date
A

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
 
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
 
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
Click to expand...

Thanks Dave..let me try this.
 
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
Click to expand...

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



:
 
Back
Top