i is an arraylist of names. j is using that list as tab names. The problem Im running into is that if that range of cells has an empty cell in it -- or if the name in the array list doesn't exist how would I "skip that name" (or empty cell) and go to the next loop? I think it would be under j, but I'm not sure where to begin other than an if statement? not sure how to write an if statement for that either?

Set Tail = New ArrayList

Dim NewestEntry As Integer
Dim Apple As String
        
For i = 0 To 20 Step 3 'Tail #/Number of Tails

    Apple = Worksheets("StepBrief").Cells(i   6, 3).Value
    Tail.Add Apple
                        
Next i

For j = 0 To 20 'Number of Tail # Cells
        
            NewestEntry = Worksheets(Tail(j)).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

CodePudding user response：

Skip Blanks and Existing Values in ArrayList

• Learn about the ArrayList on this Excel MacroMastery's webpage.

Option Explicit

Sub TestArrayList()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("StepBrief")
   
    Dim Tail As ArrayList: Set Tail = New ArrayList ' (early binding)
    ' If you don't want to create a reference to 'mscorlib.dll' since
    ' at this moment the array list's intelli-sense doesn't work anyway,
    ' instead, you could use (late binding):
    'Dim Tail As Object: Set Tail = CreateObject("System.Collections.ArrayList")
    
    Dim Apple As String
    Dim i As Long
                    
    For i = 6 To 26 Step 3
        Apple = CStr(ws.Cells(i, "C").Value)
        If Len(Apple) > 0 Then ' is not blank
            If Not Tail.Contains(Apple) Then ' is not already in array list
                Tail.Add Apple
            'Else ' already in array list; do nothing
            End If
        'Else ' is blank; do nothing
        End If
    Next i

    ' Some examples of what to do with it:

    ' Print the contents of 'Tail'.
    
    Dim Item As Variant
    
    For Each Item In Tail
        Debug.Print Item
    Next Item
    
    ' Write the values from 'Tail' to an array.
    Dim Arr As Variant: Arr = Tail.ToArray
    
    ' Print the contents of the array.
    For i = 0 To UBound(Arr)
        Debug.Print i, Arr(i)
    Next i

    ' Print the contents of the array as a comma-separated list using 'Join'
    ' (since the elements are strings).
    Debug.Print Join(Arr, ",")
    
End Sub