Application defined or object defined error on code

Question

I get an error on this line in my code, any ideas what the issue may be?

Intersect(.UsedRange, .UsedRange.Offset(1)).SpecialCells(12).EntireRow.Delete

Here is the rest of the code:

Sub DefineDL_IDL() Dim wbTHMacro As Workbook, wsRegulares As Worksheet, wsRegularesDemitidos As Worksheet, wsTempActivos As Worksheet, _ wsTempJA As Worksheet, wsTempFit As Worksheet, wsTempDemitidos As Worksheet, wsPS As Worksheet, wsResultados As Worksheet, _ wsDLList As Worksheet, wssheet As Worksheet, count_DL As Integer, count_IDL As Integer Dim x&, r As Long '*************REGULARES*********** Sheets("Regulares").Select 'Debug.Print xlToRight 'Sheets("Raw").Copy before:=Sheets(2) With Sheets("Regulares") '.Name = "Final2" .UsedRange.AutoFilter 9, "INATIVE" Intersect(.UsedRange, .UsedRange.Offset(1)).SpecialCells(12).EntireRow.Delete r = WorksheetFunction.CountA(.Range("A:A")) .UsedRange.AutoFilter .Range("J:J").Insert xlToRight .Range("J1").Value = "Real MO" .Range("K:K").Cut .Range("I:I").Insert xlToRight .Range("Q:Q").Cut .Range("I:I").Insert xlToRight .Range("L2:L" & r).FormulaR1C1 = "=VLOOKUP(RC[-3],'DL List'!C[-11]:C[-10],2,0)" .Range("L2:L" & r).Value = .Range("L2:L" & r).Value For x = 2 To r If Range("L" & x).Text = "#N/A" Then 'If Range("K" & x).Value = "DL" Then ' Range("L" & x).Value = "DL" 'Else: Range("L" & x).Value = "IDL": End If Range("L" & x).Value = "IDL" End If Next x End With count_DL = Application.WorksheetFunction.CountIf(ActiveSheet.Range("L:L"), "DL") count_IDL = Application.WorksheetFunction.CountIf(ActiveSheet.Range("L:L"), "IDL") Worksheets("Resultados").Range("B17") = count_DL Worksheets("Resultados").Range("C17") = count_IDL

Edited the question a bit but basically the code is supposed to make changes to a sheet on a workbook and count number of employees DL vs IDL... — William
– William, Commented Jun 18, 2014 at 13:14
Have you looked at the usage of intersect? msdn.microsoft.com/en-us/library/office/… — Ravi Yenugu
– Ravi Yenugu, Commented Jun 18, 2014 at 13:16

Community · Accepted Answer · 2017-05-23 12:20:26Z

Your expression works on my test worksheet so the problem must be something about your data.

I do not like stringing properties together like this because the objective becomes very unclear. Worse, if it fails, you do not know where is fails.

Try replacing the statement with this:

Dim rng As Range Debug.Print .UsedRange.Address Debug.Print .UsedRange.Offset(1).Address Set rng = Intersect(.UsedRange, .UsedRange.Offset(1)) Debug.Print rng.Address Debug.Print rng.SpecialCells(12).Address Debug.Print rng.SpecialCells(12).EntireRow.Address rng.SpecialCells(12).EntireRow.Delete

Step through this code to make sure each range is as you expect.

My guess that that there are no visible cells in the range so you are attempting to delete Nothing.

Edit Extra information about finding last row of worksheet.

There are a variety of methods of finding the last used row or column of a worksheet. None work in every situation but UsedRange is the method least likely to give the result you expect.

The most popular method of finding the last row, judging by answers here, is:

RowLast = .Cells(Rows.Count,9).End(xlUp).Row

This is the VBA equivalent of placing the cursor in the bottom cell of column 9 and clicking Ctrl+Up. RowLast will be set to the last row with a value in column 9 unless you have a value in the bottom cell. For this method to be of any use, there must be a value in the specified column of the last used row.

Find is a reliable method of finding the last value by either row or column.

SpecialCells is another useful method.

This answer of mine VBA Dynamic Ranges includea a macro, FindFinal, which demonstrates how these methods can fail to give the result you might expect. If you wish to fully understand the issues, step through this macro studying what happens.

I get the same error on the second line in the code: Debug.Print .UsedRange.Offset(1).Address basically what the macro needs to do is delete the all those rows that it filtered as "INATIVE" from the previous line .UsedRange.AutoFilter 9, "INATIVE"
What was the address of UsedRange? UsedRange.Offset(1) adds 1 to the start and end row of UsedRange. This would not be allowed if UsedRange included the bottom row of the worksheet. UsedRange includes every row with a non-default format. If you have selected All and then set font, for example, UsedRange will be the entire sheet.
Basically the range will be different from one month to the other... Would it be better to change the code to delete all rows within column 9 based on the "INATIVE" instead of doing the filter? If so how would that be done?

Collectives™ on Stack Overflow

Application defined or object defined error on code

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related