How do I programmatically build a range?

[JudgeMental]

I have a fixed range of cells (41) that I need to chart. The user may or may
not fill all the final cells. I need to exclude these unused cells at the end
from the chart.

Dim LastColReservedCell As Integer
Dim LastActiveCell As Integer
Dim MyRange As Range

LastActiveCell = 0
LastColReservedCell = 41

For Each c In Worksheets("Charts").Range _
("B1:B41").Cells

If c.Value < 1 Then
Blanks = Blanks + 1
Else
Blanks = Blanks
End If
Next

LastActiveCell = LastColReservedCell - Blanks

'Here is where the problem starts. Trying to build the range generates an
error 13.
MyRange = "A1:" + LastActiveCell

'If I can build a valid Range, I would like to use it as shown below:

ActiveChart.SetSourceData Source:=Sheets("Charts").Range(MyRange), PlotBy _
:=xlColumns

'Using (MyRange) instead of ("A1:B41")

Is this possible?

Thank You for your help.

 

[Bob Phillips]

MyRange = "A1:A" & LastActiveCell


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gary''s Student]

n = Cells(42, "b").End(xlUp).Row
MyRange = "A1:B" & n

 

[Rick Rothstein \(MVP - VB\)]

You have MyRange declared as being of type Range, but you are trying to
assign a text to it... they are not compatible. As a Range, you need to Set
another Range object to it. Also, the text you are trying to assign is
incorrect; it needs a column reference after the colon since the only thing
in LastActiveCell is a number. Instead of this...

'Here is where the problem starts. Trying to build the range generates an
error 13.
MyRange = "A1:" + LastActiveCell

do it this way...

Set MyRange = Range("A1:B" + LastActiveCell)

By the way, you can solve for the last used cell directly without a loop...

LastActiveCellRow = Range("B42").End(xlUp).Row

Note that you have to start looking from below the last piece of data. In
your case, data can be filled down to B41, so start looking from below that
(hence, my use of B42). In the general case, assuming no data is located
below the cell you want to find (B41 in this case), you can use this...

LastActiveCellRow = Cells(Rows.Count, "B").End(xlUp).Row

Rick

 

[Gary Keramidas]

or another way that may show another method.

Sub test()
Dim MyRange As Range
Dim lastactivecell As Range
Set lastactivecell = Range("B42").End(xlUp)
Set MyRange = Range("A1:" + lastactivecell.Address)
End Sub