Run Time Error 91 - Object Variable or Block Variable Not Set

[zalik22]

Not sure what is wrong, can someone please help? Getting error at "' Find which column "Local Start Time" appears in" step.

Sub MacroKeywords()
' Keyboard Shortcut: Ctrl+i
'Application.ScreenUpdating = False

Dim rngKeywordsFound    As Range, _
    rngAgentGroup   As Range


'Start of function to delete unwanted rows and columns
With ActiveSheet.Cells
Set rngAgentGroup = .Find("Agent Group", LookIn:=xlValues, LookAt:=xlPart)
Set rngKeywordsFound = .Find("Keywords Found", LookIn:=xlValues, LookAt:=xlWhole)
If Not rngAgentGroup Is Nothing And Not rngKeywordsFound Is Nothing Then
'AgentGroup and KeywordsFound found
If rngKeywordsFound.Column = rngAgentGroup.Column - 1 Then
'KeywordsFound column is adjacent to AgentGroup, clear only columns left of rngKeywordsFound
Range(Cells(1, 1), Cells(1, rngKeywordsFound.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
ElseIf rngKeywordsFound.Column = 1 Then
'KeywordsFound is first column. Only clear columns between rngKeywordsFound and AgentGroup
Range(Cells(1, rngKeywordsFound.Column + 1), Cells(1, rngAgentGroup.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
Else
'KeywordsFound column is not adjacent to AgentGroup. Clear columns between and then columns to left of rngKeywordsFound
Range(Cells(1, rngKeywordsFound.Column + 1), Cells(1, rngAgentGroup.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
Range(Cells(1, 1), Cells(rngKeywordsFound.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
End If
Range("A1:A" & rngKeywordsFound.Row - 1).EntireRow.Delete Shift:=xlToLeft
End If
End With
'End of function to delete unwanted rows and columns

    Dim sortAdd As String
    Dim sortRange As Range

'   Find which column "Local Start Time" appears in
Rows("1:1").Find(What:="Local Start Time", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
sortAdd = ActiveCell.Address(0, 0)

' Convert entries in Date column to valid dates
Columns(ActiveCell.Column).TextToColumns Destination:=Range(sortAdd), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 3), TrailingMinusNumbers:=True

' Format columns
Columns(ActiveCell.Column).NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

' Set sort range by using current region
Range("A1").CurrentRegion.Sort _
        key1:=Range(sortAdd), order1:=xlAscending, Header:=xlYes

End Sub

 

[Fluff]

In that case, that's the problem as your code is running on the active sheet. ie the sheet with the button.
What is the name of the sheet it needs to run on?

 

[zalik22]

Sheet with macro is "Macro"
Sheet with data where data is manipulated is "Calls"

 

[Fluff]

Ok, try this

Sub MacroKeywords()
' Keyboard Shortcut: Ctrl+i
'Application.ScreenUpdating = False

   Dim rngKeywordsFound    As Range, _
       rngAgentGroup   As Range
   
   
   'Start of function to delete unwanted rows and columns
   With Sheets("calls")
      Set rngAgentGroup = .Cells.Find("Agent Group", LookIn:=xlValues, LookAt:=xlPart)
      Set rngKeywordsFound = .Cells.Find("Keywords Found", LookIn:=xlValues, LookAt:=xlWhole)
      If Not rngAgentGroup Is Nothing And Not rngKeywordsFound Is Nothing Then
         'AgentGroup and KeywordsFound found
         If rngKeywordsFound.Column = rngAgentGroup.Column - 1 Then
            'KeywordsFound column is adjacent to AgentGroup, clear only columns left of rngKeywordsFound
            .Range(.Cells(1, 1), .Cells(1, rngKeywordsFound.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
         ElseIf rngKeywordsFound.Column = 1 Then
         'KeywordsFound is first column. Only clear columns between rngKeywordsFound and AgentGroup
            .Range(.Cells(1, rngKeywordsFound.Column + 1), .Cells(1, rngAgentGroup.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
         Else
         'KeywordsFound column is not adjacent to AgentGroup. Clear columns between and then columns to left of rngKeywordsFound
            .Range(.Cells(1, rngKeywordsFound.Column + 1), .Cells(1, rngAgentGroup.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
            .Range(.Cells(1, 1), .Cells(rngKeywordsFound.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
         End If
         .Range("A1:A" & rngKeywordsFound.Row - 1).EntireRow.Delete Shift:=xlToLeft
      End If
   'End of function to delete unwanted rows and columns
   
      Dim sortAdd As String
      Dim sortRange As Range
      
      '   Find which column "Local Start Time" appears in
      sortAdd = .Range("1:1").Find("Local Start Time", , , xlPart, , xlNext, False, , False).Address(0, 0)
      ' Convert entries in Date column to valid dates
      .Range(sortAdd).EntireColumn.TextToColumns Destination:=.Range(sortAdd), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 3), TrailingMinusNumbers:=True
      
      ' Format columns
      .Range(sortAdd).EntireColumn.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
      
      ' Set sort range by using current region
      .Range("A1").CurrentRegion.Sort _
              key1:=.Range(sortAdd), order1:=xlAscending, Header:=xlYes
   End With

End Sub

 

[zalik22]

Any way I can do it with using sheets("Calls").Select? I still get the error at the ' Set sort range by using current region part at the very end.

Sub Macro1()
' Keyboard Shortcut: Ctrl+i
'Application.ScreenUpdating = False

Dim rngKeywordsFound As Range, _
rngAgentGroup As Range


'Start of function to delete unwanted rows and columns
Sheets("Calls").Select
Dim fCell As Range
' Find Agent Group
Cells.Find(What:="Keywords Found", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 0
Set fCell = ActiveCell
' Delete rows above
If fCell.Row > 1 Then
Rows("1:" & fCell.Row - 1).Delete
End If
' Delete columns to the left
If fCell.Column > 1 Then
Range(Cells(1, 1), Cells(1, fCell.Column - 1)).EntireColumn.Delete
End If
'End of function to delete unwanted rows and columns

Dim sortAdd As String
Dim sortRange As Range

' Find which column "Local Start Time" appears in
Rows("1:1").Find(What:="Local Start Time", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
sortAdd = ActiveCell.Address(0, 0)

' Convert entries in Date column to valid dates
Columns(ActiveCell.Column).TextToColumns Destination:=Range(sortAdd), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 3), TrailingMinusNumbers:=True

' Format columns
Columns(ActiveCell.Column).NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

' Set sort range by using current region
Range("A1").CurrentRegion.Sort _
key1:=Range(sortAdd), order1:=xlAscending, Header:=xlYes
end sub

 

[Fluff]

Did you get an error with the code I posted?

 

[zalik22]

Figured out the issue, there are a few blank columns. Is there code I can put to delete the blank colums in the spreadsheet? Thanks!

 

[zalik22]

Figured out the issue, there are a few blank columns. Is there code I can put to delete the blank colums in the spreadsheet? Thanks!

If I manually delete the blank columns in the data, the macro runs perfectly. Is there a way I can put in code to delete all the blank columns? There is a set of 2 blank columns and further along the file there is another blank column.

 

[Fluff]

With the code I supplied you can use

      .UsedRange.Sort _
              key1:=.Range(sortAdd), order1:=xlAscending, Header:=xlYes

 

[zalik22]

Sorry it didn't work.

this is the working code I have if I manually delete any blank columns in the spreadsheet (there are two sets of columns that are blank within the data)

Sub Macro1()
' Keyboard Shortcut: Ctrl+i
'Application.ScreenUpdating = False

Dim rngKeywordsFound As Range, _
rngAgentGroup As Range


'Start of function to delete unwanted rows and columns
Sheets("Calls").Select
Dim fCell As Range
' Find Agent Group
Cells.Find(What:="Keywords Found", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 0
Set fCell = ActiveCell
' Delete rows above
If fCell.Row > 1 Then
Rows("1:" & fCell.Row - 1).Delete
End If
' Delete columns to the left
If fCell.Column > 1 Then
Range(Cells(1, 1), Cells(1, fCell.Column - 1)).EntireColumn.Delete
End If
'End of function to delete unwanted rows and columns

Dim sortAdd As String
Dim sortRange As Range

' Find which column "Local Start Time" appears in
Rows("1:1").Find(What:="Local Start Time", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
sortAdd = ActiveCell.Address(0, 0)

' Convert entries in Date column to valid dates
Columns(ActiveCell.Column).TextToColumns Destination:=Range(sortAdd), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 3), TrailingMinusNumbers:=True

' Format columns
Columns(ActiveCell.Column).NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

' Set sort range by using current region
Range("A1").CurrentRegion.Sort _
key1:=Range(sortAdd), order1:=xlAscending, Header:=xlYes

end sub

 

[Fluff]

What is the point of asking for help, if you ignore the help that is given?