We help IT Professionals succeed at work.

Excel VBA Dynamic range

desmondwkng
desmondwkng asked
on
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.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

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

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

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

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

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

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

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

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
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

Author

Commented:
thanks so much

Explore More ContentExplore courses, solutions, and other research materials related to this topic.