Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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.
0
desmondwkng
Asked:
desmondwkng
  • 6
  • 5
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
desmondwkngAuthor Commented:
thanks so much
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now