Im getting an Application defined or Object defined Error on a macro

Phantasm

Board Regular
Joined
Nov 2, 2007
Messages
58
I have no idea why I am getting an error on this. It was working fine earlier. The error happens on the line that is bold.

Rich (BB code):
Sub SHIPJOB(control As IRibbonControl)

With Sheets("JOBS IN PROCESS NEW")

If .AutoFilterMode Then .AutoFilterMode = False

.Range("A1:O1").AutoFilter 15, "x"

.AutoFilter.Range.Offset(1).Copy Sheets("SHIPPED ORDERS").Range("A" & Rows.Count).End(xlUp).Offset(1)

.AutoFilter.Range.Offset(1).EntireRow.Delete

.AutoFilterMode = False

If Not Worksheets("JOBS IN PROCESS NEW").Range("A1:O1").AutoFilter Then

Worksheets("JOBS IN PROCESS NEW").Range("A1:O1").AutoFilter

End If

ActiveWorkbook.Sheets(3).Activate

Rows("2:150").RowHeight = 16.5

End With

End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,728
Office Version
  1. 365
Platform
  1. Windows
Your code does not fail for me
Does sheet "Shipped Orders" exist? Is the spelling correct? (think it would give a different error in any case)
Test this above the line that fails
Code:
MsgBox .AutoFilter.Range.Address
 
Last edited:

Phantasm

Board Regular
Joined
Nov 2, 2007
Messages
58
Your code does not fail for me
Does sheet "Shipped Orders" exist? Is the spelling correct? (think it would give a different error in any case)
Test this above the line that fails
Code:
MsgBox .AutoFilter.Range.Address


$A:$O

Is what I get. But, you know what, I formatted the sheet as a table...could that have anything to do with it?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
How about ...

Code:
  .Range("A1").CurrentRegion.AutoFilter 15, "x"
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,728
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It may well be related to the table - I suspect that was a recent change!

How can VBA offset a row when the range already includes EVERY rows in the worksheet? That is why it is failing
Code:
.AutoFilter.Range.Offset(1)
Try using this as your range - without any offset - it comprises data only
Code:
ActiveSheet.ListObjects(1).DataBodyRange

Try recording a macro when you filter the table

Read this useful guide
https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
If you have the whole sheet as a table, and the filter range is A: O, then when you increase the rank in a row offset(1), it exceeds the number of rows, so it sends the error.
It can be solved in this way.

Code:
Sub SHIPJOB(control As IRibbonControl)
    With Sheets("JOBS IN PROCESS NEW")
        If .AutoFilterMode Then .AutoFilterMode = False
        .Range("A:O").AutoFilter 15, "x"
        .AutoFilter.Range.Offset(1)[COLOR=#0000ff].Resize(.AutoFilter.Range.Rows.Count - 1)[/COLOR].Copy Sheets("SHIPPED ORDERS").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).[COLOR=#0000ff]Resize(.AutoFilter.Range.Rows.Count - 1)[/COLOR].EntireRow.Delete
        .AutoFilterMode = False
        If Not Worksheets("JOBS IN PROCESS NEW").Range("A1:O1").AutoFilter Then
            Worksheets("JOBS IN PROCESS NEW").Range("A1:O1").AutoFilter
        End If
        ActiveWorkbook.Sheets(3).Activate
        Rows("2:150").RowHeight = 16.5
    End With
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,728
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@DanteAmor

I cannot test but should the resize not come before the offset?

this
Code:
.AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1)
or this??
Code:
.AutoFilter.Range.Resize(.AutoFilter.Range.Rows.Count - 1).Offset(1)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
@DanteAmor

I cannot test but should the resize not come before the offset?

this
Code:
.AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1)
or this??
Code:
.AutoFilter.Range.Resize(.AutoFilter.Range.Rows.Count - 1).Offset(1)


Do not worry, I can test it for you.
This form is correct:


Code:
.AutoFilter.Range.Offset(1).Resize(.AutoFilter.Range.Rows.Count - 1).Copy

This form is wrong:


Code:
.AutoFilter.Range.Resize(.AutoFilter.Range.Rows.Count - 1).Offset(1)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
If you have the whole sheet as a table, and the filter range is A: O, then when you increase the rank in a row offset(1), it exceeds the number of rows, so it sends the error.

A small detail, return this "A:O" to this "A1:O1" on the next line:

Code:
.Range("A1:O1").AutoFilter 15, "x"
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,728
Office Version
  1. 365
Platform
  1. Windows
deleted by Yongle
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,817
Messages
5,525,068
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top