0

I'm VERY new to VB.

I want to be able to create an xlBarClustered chart using data in a worksheet. Specifically, the data resides in columns E and F, with row 1 as the header row. I don't know how many rows of data will be in the data each time. I have figured out how to set a range:

    Dim rng as range
    Set rng  = Sheets("Recording (2)").Range("E1:F5")

I can't figure out how to set that range to an undetermined number of rows.

I thought I might be able to name a range as follows but don't know how to use that to Set the range:

   Range("E1:F1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Names.Add Name:="FirstNamedRange", RefersToR1C1:= _
        "=Recording!R1C5:R5C6"

Here's the code I have the currently works. I'll be making format changes to it later but this is what I want to change to unknown number of rows instead of the specific rows currently:

Sub CreateChart()
'
' CreateChart Macro
' Creates a chart in one worksheet
' Want the range to start with E1, go to the right and down.
' first row  is a header row.

'
       
        
    Dim rng As Range
    Dim cht As Chart
    
    'Set cht = Sheets("Chart 1")
    'Set cht = ActiveChart
    
    Set cht = Sheets("Chart 1").Shapes.AddChart2.Chart
    Set rng = Sheets("Recording (2)").Range("E1:F5")
   
    cht.SetSourceData Source:=rng
    cht.ChartType = xlBarClustered
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
StaceyB
  • 35
  • 4
  • It sounds like you just need to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Dec 03 '20 at 19:17
  • Yes, I would like to find the last row but I'm going to implement this on many worksheets and don't want to do it manually. Do you know how I can put code in that will do this? – StaceyB Dec 03 '20 at 19:22
  • 1
    `lastRow = Cells(Rows.Count, "E").End(xlUp).Row`? – BigBen Dec 03 '20 at 19:23
  • Thank you BigBen. Do you mind helping me further with what may seem very basic? – StaceyB Dec 03 '20 at 20:32
  • How do I use "lastRow" when assigning the rng? Set rng = Sheets("Recording (2)").Range(lastRow) I'll try this to see... – StaceyB Dec 03 '20 at 20:34
  • `Set rng = Sheets("Recording (2)").Range("E1:F" & lastRow)`. – BigBen Dec 03 '20 at 20:35
  • Beautiful! Thanks!! That helps me know how to use variables as part of my row references for other uses. – StaceyB Dec 03 '20 at 20:56

0 Answers0