Get last cell with data in column in excel 2007

Question

I'm trying to write a macro to create graphs in excel 2007. I don't know the number of cells that will be in the range for one of the series of data (it could be anywhere from 50 - 1000). I've googled this and I've found answers but they are all over the map and the few I've tried haven't helped me at all.

I'm a newb at vba macros but am an experienced programmer.

I've found examples such as:

Sub FindLast2()
    x = ActiveSheet.UsedRange.Rows.Count
    ActiveCell.SpecialCells(xlLastCell).Select
End Sub

I'm not sure if this works & if it does work how would I incorporate that into my macro

Here's my macro as it stands now:

Sub temp_graph_5()
'
' temp_graph_5 Macro
'

'
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(2).Select
    Sheets(2).Name = "Temperature"
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Sheets(1). _
        Range("B2:B324")
    ActiveChart.SeriesCollection(1).Name = "=""Temperature"""

End Sub

The 'B324' is the section that I need to be variable.

Any help is greatly appreciated.

Answer 1

This code may help achieve what you need:

 Sub temp_graph_5()
    Dim myRng As Range
    Dim lastCell As Long

    //Get range to be plotted in chart
    lastCell = Worksheets(1).Range("B2").End(xlDown).Row
    Set myRng = Worksheets(1).Range("B2:B" & lastCell) 

    //Add worksheet and name as "Temperature"
    Dim newSheet As Worksheet

    Set newSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    newSheet.Name = "Temperature"
    newSheet.Select

    //Add a new chart in Temperature and plot values from sheet 1
    Charts.Add

    With ActiveChart
        .ChartType = xlLine
        .SetSourceData Source:=myRng, PlotBy:=xlColumns
        .Location Where:=xlLocationAsObject, Name:="Temperature"
    End With

End Sub