Quick Column Related Question...


New Member
Aug 21, 2002
This may be trivial to some of you but, here goes...

I have a worksheet which comprises of a sheet of a lot of raw data from which I am trying to automate the creation of some charts. However, the amount of rows and columns can vary so here is my dilemma.

The charts always use the A column as the X-Axis but, the Y-Axis comprises of two adjacent columns which move along from chart to chart...e.g. B & C for the first one, D & E for the next and so on. I was thinking of writing a while loop searching for an empty column as it moves along (thus indicating the end) however, it is this moving along the columns which is causing the headache. The code I have so far is a little basic but, here is a cut and paste:

Sub Chart()

Dim ChartNo As Byte
Dim FullCellRef As String
Dim CellRef As String
Dim SheetName As String
Dim Fruni As String
Dim CurrentCell As String
Dim ColumnNo As String
Dim ChartRange As Integer

' Defining a variable that increments to allow sheet renaming.
ChartNo = 1
' Trying to define a variable to use to move the column along.
ColumnNo = "A:A"

While ActiveCell.Value <> ""

ActiveChart.ChartType = xlLine

' This is the line where I am having the problem, how can I replace the column letters with a variable that can be incremented to allow moving along two columns at a time..?? Remember that "A:A" is always the X-Axis.

ActiveChart.SetSourceData Source:=Sheets("sheet1").Range( _
"A:A, B:B, C:C"), PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.HasLegend = True
Selection.Position = xlRight


FullCellRef = Left(ActiveCell.Value, 13)
Fruni = Right(Right(FullCellRef, 3), 2)
CellRef = Left(FullCellRef, 10) & Fruni
Sheets("Chart" & ChartNo).Name = CellRef
ActiveCell.Offset(0, 2).Select

ChartNo = ChartNo + 1
ColumnNo = ColumnNo + 2


End Sub

Thanks in advance for any help given.:)

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Have you tried:

ActiveChart.SetSourceData Source:=Sheets("sheet1").Range( _
"A:A, B:C"), PlotBy:=xlColumns

Upvote 0
That would chart both columns but, I need a way to increment that every time through the loop. e.g. "B:C" then becomes "D:E" and "F:G" and so on. It doesn't have to be done with letters if it can be done more easily using cell index references or some other such numeric expression. My thoughts were that it would be easy if the table only went from column A to Z but, how does Z+1 become AA in that formula..?? I agree that it could be done but, the coding seems a little unnecessary if it can be done more easily with numbers. :)

Thanks in advance,

Upvote 0
Check out the CELLS property. I had to key in the word "cell" for it to come up in help:<pre>
Cells Property Example

This example sets the font size for cell C5 on Sheet1 to 14 points.

Worksheets("Sheet1").Cells(5, 3).Font.Size = 14
This example clears the formula in cell one on Sheet1.

This example sets the font and font size for every cell on Sheet1 to 8-point Arial.

With Worksheets("Sheet1").Cells.Font
.Name = "Arial"
.Size = 8
End With
This example loops through cells A1:J4 on Sheet1.
If a cell contains a value less than 0.001, the example replaces that value with 0 (zero).

For rwIndex = 1 to 4
For colIndex = 1 to 10
With Worksheets("Sheet1").Cells(rwIndex, colIndex)
If .Value< .001 Then .Value = 0
End With
Next colIndex
Next rwIndex
This example sets the font style for cells
A1:C5 on Sheet1 to italic.

Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True
This example scans a column of data named "myRange." If a cell has the same
value as the cell immediately above it, the example displays the address of the cell
that contains the duplicate data.

Set r = Range("myRange")
For n = 1 To r.Rows.Count
If r.Cells(n, 1) = r.Cells(n + 1, 1) Then
MsgBox "Duplicate data in " & r.Cells(n + 1, 1).Address
End If
Next n</pre>


Excel 2000; Windows 2000
This message was edited by Jim North on 2002-08-22 10:51
Upvote 0
Hi Jim,

Have come up with method to workout how many rows and columns have data in and have stored this to a variable, so don't necessarily need to refer to a whole column any more. However, I still have a very frustating problem. In the following pieces of code, I cannot find a way to refer to a variable in the chart range specification. Any ideas..??

I have tried various methods:

A) By defining a union that is equal to the chart area:

ChartArea = Application.Union(Columns(1), Columns(ColumnNo), Columns(ColumnNo + 1))
ActiveChart.SetSourceData Source:=Sheets("sheet1").Range(ChartArea), PlotBy:=xlColumns

(ColumnNo is a variable containing the current column number)

B) By trying to refer to it using Cells:

I would cut and paste code into here but, I couldn't even find a combination of syntax that would actually compile.

C) By trying to use the Columns command:

ActiveChart.SetSourceData Source:=Sheets("sheet1").Range(Columns(1), Columns(ColumnNo), Columns(ColumnNo + 1)), PlotBy:=xlColumns

(As I stated before, Column 1 is always the X axis)

None of these work even though a couple of them will actually compile. I have done MsgBoxs to prove that the values being passed are correct but, still no joy.

Again, thanks in advance :)

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back