我的代码中的这一行出错，任何想法可能是什么问题？
Intersect(.UsedRange, .UsedRange.Offset(1)).SpecialCells(12).EntireRow.Delete这是代码的其余部分：
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_IDLI get an error on this line in my code, any ideas what the issue may be?
Intersect(.UsedRange, .UsedRange.Offset(1)).SpecialCells(12).EntireRow.DeleteHere 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最满意答案
您的表达式适用于我的测试工作表，因此问题必须与您的数据有关。
我不喜欢像这样将属性串在一起，因为目标变得非常不清楚。 更糟糕的是，如果它失败了，你就不知道哪里失败了。
尝试用以下代码替换语句：
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逐步执行此代码以确保每个范围都符合您的预期。
我猜这个范围内没有可见的单元格，所以你试图删除Nothing 。
编辑有关查找工作表最后一行的额外信息。
有多种方法可以找到工作表的最后使用的行或列。 在所有情况下都不起作用，但UsedRange是最不可能提供预期结果的方法。
通过这里的答案判断，最常见的查找最后一行的方法是：
RowLast = .Cells(Rows.Count,9).End(xlUp).Row这是VBA相当于将光标放在第9列的底部单元格中并单击Ctrl + Up 。 除非底部单元格中有值，否则RowLast将设置为第9行中的值的最后一行。 要使此方法有用，在最后使用的行的指定列中必须有值。
Find是一种通过行或列查找最后一个值的可靠方法。
SpecialCells是另一种有用的方法。
我的VBA动态范围的答案包括一个宏FindFinal ，它演示了这些方法如何无法提供您可能期望的结果。 如果您希望完全理解这些问题，请通过此宏来研究会发生什么。
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.DeleteStep 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).RowThis 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.
更多推荐
发布评论