How to change the row number in macro code?


R

Ram

Please see the below code nad let me know how to replace the row number with
a parameter? The following code for 32 rows in the table, suppose we have 100
rows of data at that time how we can change the row number 32 to 100?
Every time we need to change the number in the code apart from that is there
any possiblity?

Please adive on this!


Sub Graphs()
'
' Graphs Macro
' Macro recorded 2/11/2009 by iGate Global Solutions
'
' Keyboard Shortcut: Ctrl+g
'
'number of rows with formula

rws = Cells(Rows.Count, "B").End(xlUp).Row
' to get the total number of rws in the range
MsgBox rws

Range("B4").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B4:B32"),
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R4C2:R32C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R4C3:R32C3"
ActiveChart.SeriesCollection(1).Name = "=""Available Bytes"""
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R4C2:R32C2"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R4C4:R32C4"
ActiveChart.SeriesCollection(2).Name = "=""Committed Bytes"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Memory Graphs"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Available\Committed Bytes vs Elapsed
Time"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Elapsed
Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Available\Committed Bytes"
End With
ActiveChart.PlotArea.Select
Selection.Height = 254
Selection.Width = 300
Selection.Width = 322
Selection.Height = 252
Selection.Height = 242
ActiveChart.ChartTitle.Select
ActiveChart.ChartArea.Select
ActiveChart.ChartTitle.Select
Selection.Left = 75
Selection.Top = 10
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
With Selection.Interior
.ColorIndex = 17
.Pattern = xlSolid
End With
ActiveChart.Legend.Select
Selection.Left = 118
Selection.Top = 330
ActiveChart.ChartArea.Select
Sheets("Sheet1").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("E4:E32"),
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R4C2:R32C2"
ActiveChart.SeriesCollection(1).Name = "=""Pages/Sec"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Memory Graphs"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Pages/Sec vs Elapsed Time"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Elapsed
Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Pages/Sec"
End With
ActiveChart.ChartTitle.Select
Selection.Left = 85
Selection.Top = 4
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").IncrementLeft 152.7
ActiveSheet.Shapes("Chart 1").IncrementTop -51.75
ActiveSheet.Shapes("Chart 1").ScaleWidth 0.85, msoFalse, _
msoScaleFromBottomRight
ActiveChart.ChartTitle.Select
Selection.Top = 3
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").IncrementLeft -4.68
ActiveSheet.Shapes("Chart 1").IncrementTop -7.29
ActiveSheet.Shapes("Chart 1").ScaleHeight 0.88, msoFalse,
msoScaleFromTopLeft
ActiveChart.Legend.Select
Selection.Left = 218
Selection.Top = 184
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
Selection.Left = 47
Selection.Top = 30
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
Selection.Left = 40
Selection.Width = 190
Selection.Width = 224
Selection.Top = 24
Selection.Height = 146
Selection.Height = 157
Selection.Left = 17
Selection.Width = 248
ActiveWindow.Visible = False
Windows("Sample Report_Templete_Final_10Feb.xls").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
Selection.Font.ColorIndex = 6
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 6
.Background = xlAutomatic
End With
ActiveChart.ChartArea.Select
ActiveChart.ChartTitle.Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.ChartArea.Select
ActiveChart.ChartTitle.Select
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
With Selection.Interior
.ColorIndex = 17
.Pattern = xlSolid
End With
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Windows("Sample Report_Templete_Final_10Feb.xls").Activate
Sheets("Sheet1").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("F4:F32"),
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R4C2:R32C2"
ActiveChart.SeriesCollection(1).Name = "=""Total Bytes/Sec"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Network Graphs"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Total Bytes/Sec vs Elapsed Time"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Elapsed
Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Network\Total Bytes/Sec"
End With
ActiveChart.ChartTitle.Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
With Selection.Interior
.ColorIndex = 17
.Pattern = xlSolid
End With
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Top = 441
ActiveChart.ChartArea.Select
Sheets("Sheet1").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:O32"),
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R4C2:R32C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R4C8:R32C8"
ActiveChart.SeriesCollection(1).Name = "=""% Disk Time"""
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R4C10:R32C10"
ActiveChart.SeriesCollection(2).Name = "=""Disk Bytes/Sec"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Disk Graphs"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "% Disk Time\Disk Bytes/Sec vs Elapsed
Time"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Elapsed
Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"% Disk Time\Disk Bytes/Sec"
End With
Sheets("Sheet1").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:O32"),
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R4C2:R32C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R4C11:R32C11"
ActiveChart.SeriesCollection(1).Name = "=""% Processor Time"""
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R4C12:R32C12"
ActiveChart.SeriesCollection(2).Name = "=""Thread Count"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Process Graphs"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = _
"% Processor Time\Thread Count vs Elapsed Time"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Elapsed
Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Process % Processor Time\Thread Count"
End With
Sheets("Sheet1").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:O32"),
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R4C2:R32C2"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R4C13:R32C13"
ActiveChart.SeriesCollection(1).Name = "=""% Processor Time"""
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R4C15:R32C15"
ActiveChart.SeriesCollection(2).Name = "=""Interrupts/Sec"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Processor Graphs"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = _
"% Processor Time\Interrupts per Sec vs Elapsed Time"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Elapsed
Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Processor % Processor Time\ Interrupts per Sec"
End With
ActiveChart.ChartTitle.Select
Selection.Font.ColorIndex = 6
Selection.Font.ColorIndex = 0
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
With Selection.Interior
.ColorIndex = 17
.Pattern = xlSolid
End With
Sheets("Process Graphs").Select
ActiveChart.ChartTitle.Select
ActiveChart.Axes(xlCategory).AxisTitle.Select
With Selection.Interior
.ColorIndex = 17
.Pattern = xlSolid
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
ActiveChart.ChartTitle.Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Sheets("Disk Graphs").Select
ActiveChart.ChartTitle.Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
With Selection.Interior
.ColorIndex = 17
.Pattern = xlSolid
End With
Sheets("Memory Graphs").Select
ActiveSheet.ChartObjects("Chart 1").Activate
End Sub
 
Ad

Advertisements

B

Bob Phillips

Something like

myVar = 100

...


ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B4:B" &
myVar),
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R4C2:R" & myVar &
"C2"


and so on
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top