VBA, search for rows number with specific values

Question

I am new to VBA and I want to ask what is the problem with the code, I want to get all the row numbers and store it as an array. The row has specific requirements which is in each row, the first letter of the first cell start with "B".

Sub rowNum()
    Dim rowNum() As Variant
    Dim wbBook As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim row As Integer
    Dim Counter As Integer
    Counter = 0
Set wbBook = ThisWorkbook
    With Workbook
        Set wsSource = .Worksheets("Sheet1")
        Set wsTarget = .Worksheets("sheet2")
    End With
    With wsSource
        Set row = .Range("A1", .Range("A1").End(xlUp)).Count
    End With
    ReDim rowNum(1 To rowN)
For Each c In wsSource.Range("A1:A" & rowN).Cells
If Left(c.Text, 1) = "B" Then Counter = Counter + 1
rowNum(Counter) = c.row
End If

End Sub

Answer 1

To have an array with all rows where the value in the first cell starts with "B" you can use a sub like this

---

Option Explicit

Public Sub GetRows()
    Dim colA As Variant, rowArr() As Long, ub As Long, i As Long, j As Long, val As String

    colA = Sheet1.UsedRange.Columns("A").Value2

    ub = UBound(colA)
    ReDim rowArr(1 To ub)

    j = 1
    For i = 1 To ub
        val = Trim(colA(i, 1))
        If Len(val) > 0 Then
            'to ignore case use: If LCase(Left(val, 1)) = "b" Then
            If Left(val, 1) = "B" Then
                rowArr(j) = i
                j = j + 1
            End If
        End If
    Next
    ReDim Preserve rowArr(1 To j - 1)
End Sub

---

The issues with your code:

• Option Explicit should always be used at the top of the module - This statement alone would have helped you find several problems
• Dim rowNum() As Variant is an array of Variants, when all that's needed is an array of Longs
• Dim row As Integer - this hides the built-in function Range().Row
  • causes problems on this line: rowNum(Counter) = c.row
  • also, all Integer data types should be replace with Long
• With Workbook - "Workbook" is not a valid object
  • valid objects: "Workbooks(i)" (collection of open files), "ActiveWorkbook", "ThisWorkbook"
• Set row = ... is invalid because row is defined as Integer
  • for basic data types like Byte, Integer, Long you assign values directly: val = 1
  • Set is needed for Object types like Workbook, Sheets, Range, Collection
• .Range("A1", .Range("A1").End(xlUp)).Count doesn't return total used rows
  • .Range("A1").End(xlUp) means that A1 is the active cell and .End(xlUp) is like pressing the Up arrow, so the result is that the active cell is still A1
  • That translates to .Range("A1", "A1").Count -> .Range(Row 1, Col 1).Count -> 1
  • To find the last row in column A use Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
  • Sheet1.Rows.Count is the last row on the sheet (1 million +)
  • so from Cells(Row=1048576, Col="A") press Up arrow to get to last cell with data
• As pointed out in the comments rowN is not declared, and not initialized
  • line ReDim rowNum(1 To rowN) fails because rowN is implicitly created as an Empty Variant
• c is not declared
• The For loop is not closed (Next is missing)
• The If statement written like that will only execute the line Counter = Counter + 1
  • The If statement has 2 forms:
    1. one line: If 1 = 1 Then "single statement" Else "other single line" (no End If)
    2. or a block of statements for each branch, all on separate lines, closed by an End If:

If 1 = 1 Then
    statement 1
    statement 2
    etc
Else
    statement 3
    statement 4
    etc
End If