Excel VBA Dynamic range

I would like to write a vba to highlight a VBA range from "A7" to Column L and with unknown end row no. (as this will change every time) and format this range with thin boarder.
desmondwkngAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

Try this:

Sub drawBorder()
Dim lRow As Long
lRow = Cells(Rows.Count, "L").End(xlUp).Row

'
    With Range("A:L")
'clear old borders
        .Borders.LineStyle = xlNone
    End With
    With Range("A7:L" & lRow)
'draw new borders
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).Weight = xlThin
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeTop).Weight = xlThin
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlThin
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeRight).Weight = xlThin
    End With

End Sub

Open in new window


The code looks for the last populated row in column L. You can change that to any other column by adjusting this line of code:

lRow = Cells(Rows.Count, "L").End(xlUp).Row

cheers, teylyn
0
desmondwkngAuthor Commented:
Hi Teylyn

I am sorry I've not explain the question clearly, I would like to set thin border to Row 52 in the attached example. But Row 52 is a variable everytime. Besides:
1. Cloumn L is a remark column and don't have too much content most of the time.
2. this is always a blank row between the total and the (body)data.

regards,

Desmond
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Did you mean to attach a file? I cannot see an attached example.

What column will have data in the last row? Use that column in the statement

lRow = Cells(Rows.Count, "L").End(xlUp).Row

For example, if column A always has data in the last row, use

lRow = Cells(Rows.Count, "A").End(xlUp).Row
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

desmondwkngAuthor Commented:
hi teylyn

not sure why the file is not attached.

Using end(xldown) will go to the bottom of the excel file exceed the required range.

regards,
ACD-TB-Template.xls
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

I never used xlDown. Please look at the code again. You changed this:

lRow = Cells(Rows.Count, "L").End(xlUp).Row

to this:

lRow = Cells(Rows.Count, "L").End(xlDown).Row

and, of course that's completely different. I just ran the code without issues using this:

lRow = Cells(Rows.Count, "A").End(xlUp).Row

Here is the complete code again:

Sub Sub_drawborder()
Dim lRow As Long
lRow = Cells(Rows.Count, "A").End(xlUp).Row
    With Range("A:L")
'clear old borders
        .Borders.LineStyle = xlNone
    End With
    With Range("A7:L" & lRow)
'draw new borders
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).Weight = xlThin
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeTop).Weight = xlThin
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlThin
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeRight).Weight = xlThin
    End With
End Sub

Open in new window


cheers, teylyn
0
desmondwkngAuthor Commented:
Hi,

I work perfectly, but when I change it to "draw all Border" it stuck at
          "With Selection.Borders(xlInsideVertical)
                  .LineStyle = xlContinuous"

Here is my new modifications :

Sub New_border()
'
' New_border Macro
' Macro recorded 12/03/2012 by Desmond Ng
'
Dim lRow As Long
lRow = Cells(Rows.Count, "A").End(xlUp).Row
    With Range("A:L")
'clear old borders
        .Borders.LineStyle = xlNone
    End With
    With Range("A7:L" & lRow)
'draw new borders
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideVertical)  <-----
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    End With

Please comment
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Why are you changing the code in the first place? The code I posted works fine, both in Excel 2003 and later.

What version of Excel are you using?

Why not just take the code I posted and run with it? You are combining macro-recorded code with the code I posted. For example:

  With Range("A7:L" & lRow)
'draw new borders
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone

The "Selection" is not qualified. It should not be used. You don't need to specify xlInsideVertical or xlDiagonalDown unless you actually want to draw these lines.

So, please, just use the code I posted. If it does not work for you, let me know what it's missing, but please DON'T mix it with recorded code, unless you know what you're doing.

cheers, teylyn
0
desmondwkngAuthor Commented:
Dear Teylyn

I'm using Excel 2003 too
Because I need a "all border" for all hightlighted  cell instead of the outside border

cheers, Desmond
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
you mean you want to have all four borders in each cell? So that you have a complete grid from A7 to the last row of data?
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
If so, use

Sub DrawBorder()
Dim lRow As Long
lRow = Cells(Rows.Count, "A").End(xlUp).Row
    With Range("A:L")
'clear old borders
        .Borders.LineStyle = xlNone
    End With
    With Range("A7:L" & lRow)
'draw new borders
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
    End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
desmondwkngAuthor Commented:
thanks so much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.