Results 1 to 4 of 4

Thread: VBA: Code for adding dynamic chart data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Code for adding dynamic chart data

    Hello, I am working on a macro to create a chart. When I use the macro recorder and add in the chart data, it seems to use the fixed cell range but what I am actually doing is using ctrl + shift + down to select the entire range which will vary depending on the chart.

    How can I get excel to check for the range before adding the chart data? I assume a for loop checking the number of rows would be one way to do it but just want to see if there's any simpler command.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA: Code for adding dynamic chart data

    Try using the CurrentRegion property of the Range object, for example, assuming that your data starts at cell A1...

    Code:
        Dim rSourceData As Range    
        Set rSourceData = Range("a1").CurrentRegion
    Hope this helps!
    Last edited by Domenic; Jun 10th, 2019 at 05:38 PM.

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Code for adding dynamic chart data

    Thanks! I was able to do this by finding the last row in column A:
    lRow = Cells(Rows.Count, "A").End(xlUp).Row


    Set Chart_Range = Sheets("Data").Range(Cells(1, "A"), Cells(lRow, "E"))

    Have not heard of current region but it looks like it can be useful in the future. The problem was that I didn't want every single column, which from my understanding is what current region does.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA: Code for adding dynamic chart data

    Yes, as per the VBA reference found here...

    "The current region is a range bounded by any combination of blank rows and blank columns."

    So yes, it will include all columns. But, if you only want the first 5 columns, you can use the Resize property...

    Code:
    Set Chart_Range = Sheets("Data").Range("a1").CurrentRegion.Resize(, 5)
    Last edited by Domenic; Jun 10th, 2019 at 06:18 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •