Help EDITING this VBA to work off of Col A to Draw Separator Borders Between Groups of Data

[ChrisOK]

I used this code in the past to draw borders based upon the NSN Stock # content of COL C.
However, I've got a different file where the NSN is in COL A.

I thought it was as easy as adjusting the COL reference of C to A but that's not working.
I think it has something to do with the "+1"...
This line of code is highlighted with the error:

vArr = Range("A1:A" & lLR + 1).Value

ERROR: Run-time error 1004: Method 'Range' of object'_Global' failed.
Anyhow know how to adjust it and explain why it didn't work so I can avoid this in the future?

 ORIGINAL CODE USING COLUMN C NEEDS TO USE COLUMN A INSTEAD
    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

In advance, THANK YOU
Chris

 

[pbornemeier]

The code mod that you made should have worked unless there were only 2 columns of data (A & B). Try this (which defines the array based on A1 current region, not C1 current region:

    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("A1").CurrentRegion.Rows.Count
    lC = Range("A1").CurrentRegion.Columns.Count
    
    ' Column A has the NSN numbers, load in array
    vArr = Range("A1:A" & 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

Note that the current region method will fail if there is any empty rows and/or empty columns of data in your data block.

This worked on my simple dataset