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

What about a ADODB.RecordSet ? Look here range-to-recordset. It can be filtered and count. — BitAccesser
– BitAccesser, Commented Jun 23, 2017 at 21:59

Graham · Accepted Answer · 2017-09-21 13:51:34Z

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

edited Sep 21, 2017 at 13:51

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Jun 23, 2017 at 23:30

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user8206890 Over a year ago

Thank you so much. I was confusing about VBA in lots of aspects. Is there any useful instruction book that I could look at?

paul bica Over a year ago

Not sure about books - I haven't used any (I learned everything I know form this site). You can also play with the recorder, and use the extensive Help that comes with the tool - it explains quite a few important details and concepts that you need to know. Also, when you do your best and can't overcome issue, ask more questions here, but make them easy to reproduce and test for others to be able to help you

user8206890 Over a year ago

Thank you so much for all the suggestions.

Collectives™ on Stack Overflow

VBA, search for rows number with specific values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related