Solved

Excel VBA Dynamic range

Posted on 2012-03-09
11
342 Views
Last Modified: 2012-03-11
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
Comment
Question by:desmondwkng
  • 6
  • 5
11 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37704318
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
 

Author Comment

by:desmondwkng
ID: 37708030
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37708091
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:desmondwkng
ID: 37708132
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37708142
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
 

Author Comment

by:desmondwkng
ID: 37708525
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37708532
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
 

Author Comment

by:desmondwkng
ID: 37708546
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37708549
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 37708550
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
 

Author Comment

by:desmondwkng
ID: 37708555
thanks so much
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question