0

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.

Community
  • 1
  • 1
Patrick
  • 3,142
  • 4
  • 31
  • 46
  • There are a few ideas here: http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro – Fionnuala Dec 10 '10 at 22:38

2 Answers2

2

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
Davesexcel
  • 6,896
  • 2
  • 27
  • 42
Alex P
  • 12,249
  • 5
  • 51
  • 70
  • thank you so much! do you know of any good places for tutorials for vba macros? – Patrick Dec 11 '10 at 22:48
  • 2
    If you want to learn VBA macros to a proficient standard I would suggest buying a book (see this post--> http://stackoverflow.com/questions/404824/). Google is you friend when troubleshooting VBA issues and some sites I have found useful are --> www.ozgrid.com / www.contextures.com / www.dailydoseofexcel.com – Alex P Dec 12 '10 at 10:16
0
sub test()
last_row_all = Range("A65536").End(xlUp).Row
msgbox last_row
end sub
Davesexcel
  • 6,896
  • 2
  • 27
  • 42
Ashwith Ullal
  • 263
  • 3
  • 10
  • This would be ok for pre-xl'07. xl'07 has more than 65536 rows. One popular method is `last_row_all = cells(rows.count,"A")end(xlup).row` Make sure you `dim last_row_all as long` – Davesexcel Oct 30 '15 at 20:51