Generate Graph Based On Set of Data Using VBA

[taykaisheng]

Hi, i'm very new in VBA.. Currently I'm on my project which required me to creating a automation tool by plotting 2 bar chart automatically based on 2 table data as shown below
However as the table data was generated out from another automation tool thus the number of rows can be increase or decrease. And the problem was due to the number of rows could be more/less than 5, I couldn't select the cells by specifying the address of the cell such as "A7" or "B6".

And these are the expected output of the chart

Hope some kind soul could help me on this..

 

[Andrew Poulsom]

I can't see your images, but if you want to select from A1 to the last nonempty cell in column A you could use:

Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Select

You may also find that this works for you:

Range("A1").CurrentRegion.Select

 

[taykaisheng]

Thanks for your help.. However now I facing another issue. I want to create a graph based on the table data which I specified using .CurrentRegion with last row of the table data should not be included. Hope you can tell me how to do this, thank you in advance

 

[Andrew Poulsom]

Example:

Sub Test()
    Dim Rng As Range
    With Range("A1").CurrentRegion
        Set Rng = .Resize(.Rows.Count - 1)
    End With
    MsgBox Rng.Address
End Sub

 

[taykaisheng]

Thank you for your quick response, do appreciate that a lot.. However I am facing another issue again. Let's say my CurrentRegion property consists of 3 columns A, B and C but I want to select entire column A and C as my data source for my chart, am I able to do so ? I know i can select column A and B as data source by implementing .Resize(CR.Columns.Count - 1). Thank you in advance

 

[Andrew Poulsom]

That would be:

Set Rng = .Resize(, .Columns.Count - 1)