Using every nth row from another document as source

[jachiavelli]

I want to use data from another file to create a graph in my excel file. I have this working (with thanks to a forum member):

Private Sub Workbook_Open()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    Workbooks.Open ThisWorkbook.Path & "\energyOutput.csv"
    
    Dim n
    ThisWorkbook.Activate
    For i = ActiveSheet.ChartObjects.Count To 1 Step -1
        ActiveSheet.ChartObjects(i).Delete
    Next i
    
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Workbooks("energyOutput.csv").Sheets("energyOutput").Range("$A1:$C" & Workbooks("energyOutput.csv").Sheets("energyOutput").Cells(65536, 1).End(xlUp).Row)
    ActiveChart.ChartType = xlLine
    
    Workbooks("energyOutput.csv").Sheets("energyOutput").Range("$A1:$C" & Workbooks("energyOutput.csv").Sheets("energyOutput").Cells(65536, 1).End(xlUp).Row).Copy

    With ActiveChart.Parent
         .Height = 325 ' resize
         .Width = 500  ' resize
         .Top = 150    ' reposition
         .Left = 230   ' reposition
     End With
     
     ActiveChart.Axes(xlCategory).TickLabelSpacing = 10
    
    Workbooks("energyOutput.csv").Close False
    
End Sub

but this uses the data from every row. I only want to use the data from every 10th or 20th row, say.

Here's an attempt I took:

Dim i As Integer
Dim s As String

i = 2
s = "A2"

Do Until IsEmpty(ActiveCell.Value)
s = s + "," + CStr(i)
i = i + 10
Loop

The string overflows and I get an error. I planned on using this string as the source.

Any ideas for other ways this might be approached? Thanks, guys

[ravishankar]

HI
Try the following codes

Sub trans()
Dim a, x, y, c, d As Integer
Dim b As String
d = 1
    a = InputBox("Enter the value of n")
    x = Cells(Rows.Count, 1).End(xlUp).Row
       For c = 1 To x Step a
       Cells(d, 24) = Cells(c, 1)
        Cells(d, 25) = Cells(c, 2)
         Cells(d, 26) = Cells(c, 3)
         d = d + 1
         Next c
             y = Cells(Rows.Count, 24).End(xlUp).Row
    b = "X1:z" & y
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Worksheets("energyoutput").Range(b), _
        PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="energyoutput"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End Sub

Run the macro. Enter the value of N. it will collate all nth values of col A to C in col X to Z and draws a Line graph
Ravi

[T-J]

I want to use data from another file to create a graph in my excel file. I have this working (with thanks to a forum member):

but this uses the data from every row. I only want to use the data from every 10th or 20th row, say.


The string overflows and I get an error. I planned on using this string as the source.

Any ideas for other ways this might be approached? Thanks, guys

In your code you are using a string formed from a range in energyOutput.csv to set the ActiveChart.SetSourceData.

ActiveChart.SetSourceData also takes a Range Object as the Source argument.

The Union method can create a small range from a big range. The small range can then be used as the Source argument.

[jachiavelli]

Thanks for the replies guys

ravishankar, thank you SO much. That's exactly what I was looking for :D