Count of Columns in string

[syps]

Hi all,

Am new in VBA, am trying to update some data daily in a columns rather thanin rows.

Below are the code that i have try but failed..

Private Sub RNC1_Click()
Dim Z As String
Z = ActiveSheet.UsedRange.Columns.Count
MsgBox (Z)
Sheets("Voicedropgraph").Select
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("RRC_drop").Range("A1:" & Z & " 1 ,A2:" & Z & " 2"), _
PlotBy:=xlRows
End Sub


Pls advice on what shoud be the "Z" if my column is variabe.
Pls advice, am thing if it is the columns.count is wrong.

Thanks in advance:yes

[Bob Phillips]

Try this



Private Sub RNC1_Click()
Dim Z As String
Dim rng As Range

Z = ActiveSheet.UsedRange.Columns.Count
MsgBox (Z)
Sheets("Voicedropgraph").Select
With Sheets("RRC_drop")
Set rng = Union(.Range("A1").Resize(, Z), .Range("A2").Resize(, Z))
End With
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=rng, _
PlotBy:=xlRows
End Sub

[syps]

Thanks alot.
It Works.

BTW can u explains

With Sheets("Voice_drop")
Set rng = Union(.Range("A1").Resize(, Z), .Range("A2").Resize(, Z))
End With

Thanks

Br

[Bob Phillips]

With Sheets("Voice_drop")

Everything that is dot qualified will now apply to this worksheet

.Range("A1").Resize(, Z)

Starting at A1, we pick up a range resized to z columns wide

.Range("A2").Resize(, Z)

Ditto A2

Union(.Range("A1").Resize(, Z), .Range("A2").Resize(, Z))

Join the two ranges together

Set rng = Union(.Range("A1").Resize(, Z), .Range("A2").Resize(, Z))

into a range object which we pass to the charting code