Avatar of desmondwkng
desmondwkngFlag for Hong Kong asked on

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.
Microsoft Excel

Avatar of undefined
Last Comment
desmondwkng

8/22/2022 - Mon
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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
ASKER
desmondwkng

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
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
desmondwkng

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
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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
ASKER
desmondwkng

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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
ASKER
desmondwkng

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
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
desmondwkng

thanks so much