I can´t range the cells interval

[KhallP]

I created a code so that each time a record was inserted into a line the program would detect the last line with values (assigned to the variable "k") and select a specific interval for the graph to update, but an error occurs, could someone help me ?



VBA Code:
Public Sub Chart_Update()

Dim k As Long

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

Error
------------------------------------------------------------------
Range("B2:B&k,E2:E&k,I2:I&k,J2:J&k,K2:K&k").Select
--------------------------------------------------------------
ActiveChart.SetSourceData Source:=Range( _
"AC_Offset_Registers!$B$2:$B$&k,AC_Offset_Registers!$E$2:$E$&k,AC_Offset_Registers!$I$2:$I$&k,AC_Offset_Registers!$J$2:$J$&k,AC_Offset_Registers!$K$2:$K$&k" _
)
End Sub



Error:

 

[Snakehips]

@KhallP You are not concatenating the range string correctly to incorporate the several instances of variable k.

Try.

Range("B2:B" & k & ",E2:E" & k & ",I2:I" & k & ",J2:J" & k & ",K2:K" & k).Select

 

[Norie]

Instead of all that concatenation you could use Intersect to get the range.

Dim rng As Range
Dim k As Long

    With Sheets("AC_Offset_Registers")
        k = .Cells(Rows.Count, "B").End(xlUp).Row
        Set rng = .Range("B2,E2,I2:K2")
        Set rng = Intersect(rng.EntireColumn, .Range("2:" & k)).Address
    End With

    ActiveChart.SetSourceData Source:=rng

 

[KhallP]

@KhallP You are not concatenating the range string correctly to incorporate the several instances of variable k.

Try.

Range("B2:B" & k & ",E2:E" & k & ",I2:I" & k & ",J2:J" & k & ",K2:K" & k).Select

That error as ocurred after that:

Code:

Public Sub Chart_Update()

Dim k As Long

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

    Range("B2:B" & k, "E2:E" & k, "I2:I" & k, "J2:J" & k, "K2:K" & k).Select
    ActiveChart.SetSourceData Source:=Range( _
        "AC_Offset_Registers!$B$2:$B$&k,AC_Offset_Registers!$E$2:$E$&k,AC_Offset_Registers!$I$2:$I$&k,AC_Offset_Registers!$J$2:$J$&k,AC_Offset_Registers!$K$2:$K$&k" _
        )
End Sub

 

[Joe4]

Compare what Snakehips showed you:

Range("B2:B" & k & ",E2:E" & k & ",I2:I" & k & ",J2:J" & k & ",K2:K" & k).Select

to what you last wrote:

Range("B2:B" & k, "E2:E" & k, "I2:I" & k, "J2:J" & k, "K2:K" & k).Select

Note that the commas must be within the quotes. You have placed them outside.