Selecting the first to last column of data when us

Question

I am compiling a bunch of data from some tests ran at work, and I want to have a macro select data from the first column all the way to the last column on the sheet. The problem is that the amount of columns may be different for every file that gets entered, so I need to look for the last column while it is being graphed.

This is what I am using so far.

Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Range( _
            "'" & fileType & "'!$A$1:$H$" & CStr(LastRowColH))

fileType is the name of the file, and it works fine finding it, but the problem comes when Excel auto places in the $A$1:$H$ range. I would like it to go from Column A to the last column with data in it. The last part of that line, which is CStr(LastRowColH)), is the closest thing I could find to code that finds the last part of something. If only there was a a CStr(LastColRow1)) code to get the last column instead of the last row.

Otherwise is there a way I can just set the $A$1:$H$ part equal to the active selection?

Any help would be appreciated!

Thanks

Answer 1

Here are a few easy ways to do this. They both assume a regular data arrangement.

Selected Range

Sub PlotUsingSelectedRange()
    Dim rng As Range
    If TypeName(Selection) = "Range" Then
        Set rng = Selection
        With ActiveSheet.Shapes.AddChart.Chart
            .ChartType = xlXYScatter
            .SetSourceData rng
        End With
    End If
End Sub

Data Region Containing Active Cell

Sub PlotUsingActiveCellCurrentRegion()
    Dim rng As Range
    If TypeName(Selection) = "Range" Then
        Set rng = ActiveCell.CurrentRegion
        With ActiveSheet.Shapes.AddChart.Chart
            .ChartType = xlXYScatter
            .SetSourceData rng
        End With
    End If
End Sub

ActiveSheet's Used Range

Sub PlotUsingActiveSheetUsedRange()
    Dim rng As Range
    If ActiveSheet.UsedRange.Cells.Count > 0 Then
        Set rng = ActiveSheet.UsedRange
        With ActiveSheet.Shapes.AddChart.Chart
            .ChartType = xlXYScatter
            .SetSourceData rng
        End With
    End If
End Sub

Answer 2

Are the source data and charts on different sheets or in different workbooks? It's not really clear from the code you posted.

Dim c As Range, sht As Worksheet, cht As Chart

Set sht = ActiveSheet
'assuming a regular block of data starting in A1
Set c = sht.Cells(1, Columns.Count).End(xlToLeft)
Set c = sht.Cells(Rows.Count, c.Column).End(xlUp)

Set cht = sht.Shapes.AddChart().Chart
cht.ChartType = xlXYScatterSmoothNoMarkers
cht.SetSourceData Source:=sht.Range(sht.Range("A1"), c) 

EDIT: or more simply just:

Set cht = sht.Shapes.AddChart().Chart
cht.ChartType = xlXYScatterSmoothNoMarkers
cht.SetSourceData Source:=sht.Range("A1").CurrentRegion