How to plot the following data using Active columns?

Question

Hello I am making a macros to plot some data, the point is to receive from the user two columns to plot, i tried first getting this values with two input boxes:

first = InputBox("first column", "Enter a Column")
two = InputBox("second column", "Enter a Column")

The problem with this approach is that i have to do the following in order to plot:

Set rng = .Range(first + "2:" + first + "84," + two + "2:" + two + "84")

The idea is to receive the value to then concatenate it with the "2" and "84" that are my range, I would like to make this macros more flexible since i am always using the same range from 2 to 84, i don't understand very well active columns, I would like to appreciate any suggestion to overcome this situation, my complete code looks as follows:

Sub ALL()
    Dim ws As Worksheet
    Dim rng As Range
    Dim objChrt As ChartObject
    Dim chrt As Chart

    Set ws = ThisWorkbook.Sheets("DATA")

    With ws
        first = InputBox("first column", "Enter a Column")
        two = InputBox("second column", "Enter a Column")
        Set rng = .Range(first + "2:" + first + "84," + two + "2:" + two + "84")
        .Shapes.AddChart
        Set objChrt = .ChartObjects(.ChartObjects.Count)
        Set chrt = objChrt.Chart

        With chrt
            .ChartType = xlColumnClustered
            .SetSourceData Source:=rng
            End With
        End With
End Sub

I don't fully get the question but maybe you can use Set rng = Application.InputBox(prompt:="Select a cell", Type:=8) msdn.microsoft.com/en-us/library/office/… — Slai
– Slai, Commented Aug 15, 2016 at 3:26
Thanks, I appreciate the support the main idea is to get the two ranges just selecting the columns on the fly. just selecting two columns with the same dimensions. — neo33
– neo33, Commented Aug 15, 2016 at 3:36

Slai · Accepted Answer · 2016-08-15 04:06:29Z

1

I still don't understand the question so maybe a picture or example might help.
Another way to get column range is like this

Set rng = ThisWorkbook.Sheets("DATA").Range("C2:C84")
MsgBox rng.Columns("B").Address(0, 0)  ' D2:D84
MsgBox rng.Columns(-1 ).Address(0, 0)  ' A2:A84
MsgBox Union( rng.Columns(2), rng.Columns(4) ).Address(0, 0)  ' D2:D84,F2:F84

If the data is for example in range B2:D4 and there are only blank cells around it, then you can get that range with Range("C3").CurrentRegion

edited Aug 15, 2016 at 4:06

answered Aug 15, 2016 at 3:59

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

neo33 Over a year ago

This was really helpful however I just want to select two columns and then call my macros to plot them,

neo33 Over a year ago

This is not exactly what I wanted but I find it really helpful I really appreciate the help.

Collectives™ on Stack Overflow

How to plot the following data using Active columns?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related