Add Border to last Row

[maninjapan]

I cant figure out the correct way to write he Range for the last row....

Sub LastRow_Border 
Dim LastRow As Long
    LastRow = Range("N" & Rows.Count).End(xlUp).Row
    Range("A & LastRow:X" & LastRow).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
End Sub

[GTO]

Is this in reference to your other thread?

Mark

[maninjapan]

Yes GTO, you could say that. the 'find the first row' thread, in regards to calculationg totals.
It would follow the following code just thought it might be better in its own thread

Private Sub CommandDailyTotal_Click() 
'   Create the Daily Total
Dim DateAdd   As String, TradSubAdd As String, f As String, NRow As Long 
NRow = Range("A" & Rows.Count).End(xlUp).Row 
DateAdd = Range("A1:A" & NRow).Address(ReferenceStyle:=xlR1C1) 
TradSubAdd = Range("L1:L" & NRow).Address(ReferenceStyle:=xlR1C1) 
f = "=if(rc[-13]<>r[1]c[-13],sumif(" & DateAdd & ",rc[-13]," & TradSubAdd & "),""zzz"")" 
With Range("N2:N" & NRow) 
        .FormulaR1C1 = f 
        .Value = .Value 
        .Replace What:="zzz", Replacement:="", LookAt:=xlWhole 
    End With 
End Sub

[georgiboy]

Sub LastRow_Border()
Dim LastRow As Long
    LastRow = Range("N" & Rows.Count).End(xlUp).Row
    Range("A" & LastRow & ":X" & LastRow).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
End Sub

[GTO]

Sub LastRow_Border()
Dim LastRow As Long
LastRow = Range("N" & Rows.Count).End(xlUp).Row
With Range("A" & LastRow & ":X" & LastRow)
        '// Note: Unless you drew lines previously in this area/range.//
        .Borders(xlDiagonalDown).LineStyle = xlNone     '<<<    From here...
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone   '<<<    to here is unnecessary
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlDouble
            .Weight = xlThick
            .ColorIndex = xlAutomatic
        End With
    End With
End Sub

[maninjapan]

cheers guys.

[dbilancione]

Hi.

I hope you are well. I am stuck with this same issue: I wish to go to the last populated row up to the last column filled (but I do not know both beforehand) and draw all borders but I am so confused with .Cells .Range .End .Offset … in some occasions I happen to have to achieve this on the active worksheet, other I need to reference another one. it is only the third day I have been playing with VBA as I wish to automate some tasks but I am puzzled. Any help would be greatly appreciated ����

dani

[SamT]

.End(xl & Up|Down|Toleft|ToRight) is same as Ctrl+Arrow
Range calls in a Worksheet's Code page automaticaly refer to Ranges on that sheet. All other Range Calls must include the Worksheet: Sheets("Tab Name"). Range("A1").BlahBlah

It is important to set the VBIDE Options
In the VBA Editor >> Menu >> Tools >> Options >> Editor Tab >> Code Settings Frame, check every box: In the Window Settings Frame, Check at least "Procedure Separator"
On the General Tab, Check: Show ToolTips, Break on Unhandled Errors, and, Compile on Demand. Note, when Troubleshooting code, you sometimes want to change the Error Trapping.

On every code page you created before setting the Options, Add "Option Explicit"" to the top of the page.

After setting Show ToolTips, you can hover over Variables and some Keywords to see their Value.

Office 365 is significantly different from all other Office Versions.

Office version 2000 to v2003 include builtin Help accessible instantly via F1. Their VBA includes 95% of all other Office Versions except Office365. If you can find an OEM of one, buy it for the Help. I still use XP almost exclusively, although I have v2007 and v2013.

All Programmers use the Variables (Lower Case) i, j, and k for counters. Never use o or O as variables. I use Rw and Col for Row and Column counters. Never use a VBA Keyword as a variable (Yes, VBA allows it, but don't.) I use Variable(Any Case) X, y, and Z strictly for TroubleShooting and Testing.

Left and Right Clicking the Vertical Bar on the left side of Code pages lets you set Breakpoints and do other good things

The DropDown boxes on Code pages are a good way to navigate code and insert Event Subs.

F2 is a good way to explore Objects and their Properties, Methods, and Events. In early versions, F1 for Help works on the F2 page.

[georgiboy]

Hi dbilancione,

Maybe the '.End' option is not the best method for what you are doing, depending on how the sheets are created you may find the 'UsedRange' more helpful for this situation.

I have provided two examples below, the first is if you know the sheet names and if there are only a few, the second loops through all sheets in a workbook but excludes the sheet 'NotThisSheet' worksheet - i added this to whow you how to exclude a sheet in a loop.

Option Explicit

Sub TestBorders()
With Sheet1
    .Range(.Range("A1"), .UsedRange).Borders.LineStyle = xlContinuous
End With
With Sheet2
    .Range(.Range("A1"), .UsedRange).Borders.LineStyle = xlContinuous
End With
End Sub


Sub TestBordersSheetLoop()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "NotThisSheet" Then
        ws.Range(ws.Range("A1"), ws.UsedRange).Borders.LineStyle = xlContinuous
    End If
Next ws
End Sub

Hope this helps

[dbilancione]

Thank georgiboy. I have tried your code but it does not exactly what I needed to do. My bad: I was not able to explain myself

I would like to draw all the borders only to the last added row. The UsedRange identifies the last used col but row1...row6 are a frozen pane where I have buttons. I have tried

With Sheet1
   .Range(.Range("A1"), .UsedRange).Borders.LineStyle = xlContinuous
End With

but I have not been successful either

Many thanks,
Dani

[jolivanes]

This thread is 13 years old. Next time start a new thread of your own and refer to the post that you think has relevance.

Sub Maybe()
With Sheets("Sheet1")
    .UsedRange.Borders.LineStyle = xlContinuous
End With
End Sub

Sub Maybe_2()
    Sheets("Sheet1").UsedRange.BorderAround 1, 2
End Sub

[SamT]

.Range(Cells(Rows.Count, 6).End(xlUp), Cells(Rows.Count, 6).End(xlUp).End(XlToRight)).BorderAround 1, 2

And this thread is now closed. If you need more info. Start a new thread and link to this thread Post#9