PDA

View Full Version : [SOLVED] Add Border to last Row



maninjapan
10-23-2008, 11:32 PM
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
10-23-2008, 11:35 PM
Is this in reference to your other thread?

Mark

maninjapan
10-23-2008, 11:41 PM
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
10-23-2008, 11:59 PM
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
10-24-2008, 12:19 AM
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
10-27-2008, 02:51 AM
cheers guys.

dbilancione
03-22-2021, 07:03 PM
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
03-22-2021, 10:17 PM
.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
03-23-2021, 01:03 AM
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
03-23-2021, 06:21 AM
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 :bug:

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 :think::think::think:

Many thanks,
Dani

jolivanes
03-23-2021, 11:31 PM
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
03-24-2021, 03:55 PM
.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