Code to select column and insert border at each change in data

[stevod]

Hi Guys im after some code that can add a border to the end of the change in data but defined by a specific column.

so in theory it would open a message box to determine the column to look down and at the end of each change in data insert a border.
The border would be from A:N for example

I've got some code that work for a defined range but i want a msg box adding to it

Sub AddBorderLineWhenValueChanges_PO()'add border
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim xrg As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each xrg In Range("AC2:AC" & LastRow)
        If xrg <> xrg.Offset(1, 0) Then
            Range("A" & xrg.Row & ":N" & xrg.Row).Borders(xlEdgeBottom).LineStyle = xlDouble
        End If
    Next xrg
    Application.ScreenUpdating = True
End Sub

any help you could give would be awesome!

 

[mikerickson]

To have the user select which column to look down.

Sub AddBorderLineWhenValueChanges_PO()'add border
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim xrg As Range
    [COLOR="#FF0000"]Dim uiColumn As Range[/COLOR]    

    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    [COLOR="#FF0000"]On Error Resume Next
    Set uiColumn = Application.InputBox("Select a column", type:=8).EntireColumn
    On Error Resume Next
    If uiColumn is Nothing Then Exit Sub: Rem user canceled
[/COLOR]
    For Each xrg In [COLOR="#FF0000"]uiColumn.Resize(LastRow, 1)[/COLOR]
        If xrg <> xrg.Offset(1, 0) Then
            Range("A" & xrg.Row & ":N" & xrg.Row).Borders(xlEdgeBottom).LineStyle = xlDouble
        End If
    Next xrg
    Application.ScreenUpdating = True
End Sub

 

[stevod]

Works well, it stops on the first line, i want it to do it for the entire page of data, xldown function

To have the user select which column to look down.

Sub AddBorderLineWhenValueChanges_PO()'add border
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim xrg As Range
    [COLOR=#FF0000]Dim uiColumn As Range[/COLOR]    

    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    [COLOR=#FF0000]On Error Resume Next
    Set uiColumn = Application.InputBox("Select a column", type:=8).EntireColumn
    On Error Resume Next
    If uiColumn is Nothing Then Exit Sub: Rem user canceled
[/COLOR]
    For Each xrg In [COLOR=#FF0000]uiColumn.Resize(LastRow, 1)[/COLOR]
        If xrg <> xrg.Offset(1, 0) Then
            Range("A" & xrg.Row & ":N" & xrg.Row).Borders(xlEdgeBottom).LineStyle = xlDouble
        End If
    Next xrg
    Application.ScreenUpdating = True
End Sub

 

[mikerickson]

Try

For each xrng in uiColumn.Resize(LastRow, 1).Cells

Also, what is the value of lastRow?

 

[stevod]

Try

For each xrng in uiColumn.Resize(LastRow, 1).Cells

Also, what is the value of lastRow?

Absolutely perfect! Thank you

 

[MARK858]

Deleted as OP has amended their previous post so now irrelevant.

 

[stevod]

another note... instead of defining the row length to apply the border could it find the end of the data itself?

Range("A" & xrg.Row & ":N" & xrg.Row).Borders(xlEdgeBottom).LineStyle = xlDouble

 

[stevod]

im not sure on how to achieve that, im still learning vba as i need to use is