PDA

View Full Version : Solved: find last row and add border to bottom > Resize selection?



mperrah
09-04-2007, 10:46 AM
fyi:
I wrote this code to find the last cell in column b,
then select across to column AK (35 columns)
and add a thin border to the bottom.
The column B has no blank spaces.

I found most of the coding here at vbaexpress. Thank you..
One question:

I saw a code that resizes the selection a specified number,
Selection.Offest(-1,0).Resize(Selection.Rows.Count+1).Select
I could not get it to work. This code works but was interested to use the number of columns instead of hard coding a column name...

Sub addbordertolastrowB()
Dim LastRowB As Long

With Range("$B:$B")
LastRowB = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

Range("B" & LastRowB & ":AK" & LastRowB).Select
' selection.offset(0, 0).resize(Selection.columns.count+35).select < this is an attempt

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Range("B" & LastRowB).Select
End Sub

mdmackillop
09-04-2007, 11:19 AM
There is a typo here
Selection.Offest(-1,0).Resize(Selection.Rows.Count+1).Select
Resize takes 2 parameters. This will extend the selection one row upwards.

Sub addbordertolastrowB()
Dim LastRowB As Long

LastRowB = Cells(Rows.Count, "B").End(xlUp).Row

With Range("B" & LastRowB).Resize(1, 35).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With

End Sub

mperrah
09-04-2007, 12:11 PM
That is totaly awesome dude
the code looks way more organized and does the job fast,
This will save a lot of time on other projects I have too.
Thanks very much.
Mark

mdmackillop
09-04-2007, 01:01 PM
Hi Mark,
Getting rid of recorded verbiage makes things much clearer, easier to follow and understand. Selection is hardly ever needed, (unless at the end after a copy/paste) so where you see something like this
Range("B" & LastRowB & ":AK" & LastRowB).Select
With Selection.Borders(xlEdgeBottom) you should just cut it out, reducing to
With Range("B" & LastRowB & ":AK" & LastRowB) _
.Borders(xlEdgeBottom)