Method 'Range' of object'_Global' failed - Use Column A instead of C - Easy Fix?

[ChrisOK]

How do I adjust this code to use Column "A" instead of "C"?
Seems like an easy fix, but I've not had any luck..
I've attempted to simply change the 4 C's to A's but get an error when it gets to this row:

   vArr = Range("C1:C" & lLR + 1).Value

(Run time error 1004: Method "Range' of object_Global failed)

    Dim rB As Range
    Dim lR As Long, lLR As Long, lC As Long
    Dim vArr As Variant
    'assuming that database can be large, will _
     work with arrays to keep speed
    
    
    'get last row and column
    lLR = Range("C1").CurrentRegion.Rows.Count
    lC = Range("C1").CurrentRegion.Columns.Count
    
    ' Column C has the NSN numbers, load in array
    vArr = Range("C1:C" & lLR + 1).Value
   
 ' now we look for changes to NSN numbers in _
      the array
    For lR = 1 To lLR
        If vArr(lR, 1) <> vArr(lR + 1, 1) Then
            With Range(Cells(lR, 1), Cells(lR, lC)).Borders(xlEdgeBottom)
                .Color = RGB(200, 0, 0)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End If
    Next lR

Originally there was a 2nd chunk of code that followed this, but I no longer need it.
I just need it to look at Column A and locate matches then group those like part numbers by drawing a border along the bottom to separate them from the next batch of numbers.

Thank you

 

[Michael M]

This works fine for me
The array line is redundant

Sub MM1()
Dim lR As Long, lLR As Long, lC As Long
    'assuming that database can be large, will _
     work with arrays to keep speed
    'get last row and column
lLR = Range("A1").CurrentRegion.Rows.Count
lC = Range("A1").CurrentRegion.Columns.Count
    ' now we look for changes to NSN numbers in the array
For lR = 1 To lLR
    If Cells(lR, 1) <> Cells(lR + 1, 1) Then
        With Range(Cells(lR, 1), Cells(lR, lC)).Borders(xlEdgeBottom)
            .Color = RGB(200, 0, 0)
            .LineStyle = xlContinuous
            .Weight = xlMedium
        End With
    End If
Next lR
End Sub

 

[wigi]

Consider the next set of simplifications:

Sub wigi()

    Dim lR As Long, vArr As Variant
    
    'assuming that database can be large, will work with arrays to keep speed
    vArr = [A1].CurrentRegion.Columns(1).Value


    ' now we look for changes to NSN numbers in the array
    For lR = 1 To UBound(vArr) - 1
        If vArr(lR, 1) <> vArr(lR + 1, 1) Then
            With Range(Cells(lR, 1), Cells(lR, UBound(vArr, 2))).Borders(xlEdgeBottom)
                .Color = RGB(200, 0, 0)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End If
    Next


End Sub

 

[Michael M]

Hi wigi
given there are no calcs, deletions, insertions, etc.
I only get a difference of 1/100th of a second difference over 10 columns and 15000 lines, between the 2 codes....

 

[wigi]

Hello Michael

In this case, it's down to personal preference, you're right.
I continued using the array approach since the OP already showed us that in that spirit.

 

[Michael M]

Yep, point taken.....I wasn't suggesting you were wrong, did it more for the exercise than anything else...