0

I loop thorough the sheets successfully until the last sheet where I get the Object Variable error and a Subscript out of range error.

I believe the issue may be in the ActiveSheet.Next.Select when it runs out of worksheets. (see code). I have tried several loop methods using codes from here and other sites with no success. I can't use ThisWorkbook because the loop is being done on another workbook.

Dim WS As Worksheet

   For Each WS In Worksheets

       LastRow = Cells(Rows.Count, "A").End(xlUp).Row
       For i = LastRow To 16 Step -1
                If Cells(i, 1) = "Actual:" Or Cells(i, 1) = "" Then
                'Delete Rows containing Actual: or empty rows
                Rows(i).EntireRow.Delete
            End If
        Next i
        ActiveSheet.Next.Select

   Next WS

This completes preparing the workbook to copy the remaining lines, which is in another procedure.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Albert Pollard
  • 35
  • 5

1 Answers1

0

Something like this should work - note it's best to avoid ActiveSheet/ActiveCell etc

Dim WS As Worksheet 

For Each WS In Worksheets
    For i = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row To 16 Step -1
        If WS.Cells(i, 1) = "Actual:" Or WS.Cells(i, 1) = "" Then
            'Delete Rows containing Actual: or empty rows
             WS.Rows(i).EntireRow.Delete
        End If
    Next i
Next WS
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • My votes aren't counted but that solved the issue! So, using the WS. before the object is the key? – Albert Pollard Apr 30 '19 at 23:49
  • You should always try to qualify all of your `Range()` or `Cells()` with a specific worksheet object, otherwise your code can be prone to bugs which might be difficult to track down. See for example: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba and https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 – Tim Williams May 01 '19 at 00:01