ExcelBanter - Building a Macro for different size trend-Charts??

Bernie,

Thank you for your advice and help. Here is the code below. If I
change the size of the data set it won't finish the macro.

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 4/13/2008 by Anthony Young
'
' Keyboard Shortcut: Ctrl+a
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "Sales Volume"
Range("B2").Select
ActiveCell.FormulaR1C1 = "1/1/1900"
Range("B2").Select
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"
Selection.AutoFill Destination:=Range("B2:B193")
Range("B2:B193").Select
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "1500-1600 SqFt Homes"
Range("D1").Select
ActiveCell.FormulaR1C1 = "1600-1700 SqFt Homes"
Columns("C:D").Select
Columns("C:D").EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll Down:=105
Range("C110").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C164").Select
ActiveWindow.SmallScroll Down:=-60
Range("C110").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut Destination:=Range("D110:D193")
Range("D110:D193").Select
ActiveWindow.SmallScroll Down:=-195
Columns("A:A").Select
Range("A1:FH193").Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("A1:D1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Sheet1!R1C1:R193C4").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "",
xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable3").Format xlReport10
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("DATE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("1500-1600 SqFt Homes"), _
"Count of 1500-1600 SqFt Homes", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("1600-1700 SqFt Homes"), _
"Count of 1600-1700 SqFt Homes", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Sales Volume"), "Sum of Sales
Volume", xlSum
Range("B3").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields ( _
"Count of 1500-1600 SqFt Homes").Function = xlAverage
ActiveSheet.PivotTables("PivotTable3").PivotSelect _
"'Count of 1600-1700 SqFt Homes'", xlDataAndLabel, True
Range("C3").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields ( _
"Count of 1600-1700 SqFt Homes").Function = xlAverage
Range("A3").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False,
False, False, _
False, False, True, True)
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Sheet4").Range("A3")
ActiveChart.Location Whe=xlLocationAsNewSheet
ActiveChart.ChartArea.Select
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).ChartType = xlColumnClustered
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 40
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Trendlines.Add(Typ e:=xlPolynomial,
Order:=4 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select
ActiveChart.SeriesCollection(3).Trendlines(1).Sele ct
With Selection
.Type = xlPolynomial
.Order = 4
.Forward = 0
.Backward = 0
.InterceptIsAuto = True
.DisplayEquation = False
.DisplayRSquared = False
.Name = "Sales Volume Trend"
End With
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Fill.OneColorGradient Style:=msoGradientVertical,
Variant:=3, _
Degree:=0.809796292057679
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 54
End With
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlPolynomial,
Order:=5 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Trendlines(1).Sele ct
With Selection
.Type = xlPolynomial
.Order = 2
.Forward = 0
.Backward = 0
.InterceptIsAuto = True
.DisplayEquation = False
.DisplayRSquared = False
.Name = "1500-1600 SqFt Trend"
End With
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Trendlines(1).Sele ct
With Selection.Border
.ColorIndex = 3
.Weight = xlThick
.LineStyle = xlContinuous
End With
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Trendlines.Add(Typ e:=xlPolynomial,
Order:=2 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False, Name:="1600-1700 SqFt Trend").Select
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Trendlines(1).Sele ct
With Selection.Border
.ColorIndex = 5
.Weight = xlThick
.LineStyle = xlContinuous
End With
ActiveChart.PlotArea.Select
ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(2).Select
Selection.Delete
ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(2).Select
Selection.Delete
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Sales Volume and Price Trend"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "DATE/
QUARTER"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales
Price"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Sales
Volume"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartArea.Select
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartArea.Select
End Sub

On Apr 15, 9:38*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Anthony,

It would help if you posted your code.

But, generally, if you get code like

Range("A2:D10")

you can change that to

Range("A2:D" & Cells(Rows.Count,4).End(xlUp).Row)

OR

Range("A2").CurrentRegion * *(but this depends on layout with headers etc)

OR

Range("A2", Range("A2").End(xlDown).End(xlToRight))

The best solution depends on your layout.

HTH,
Bernie
MS Excel MVP

"Anthony0247" wrote in message

...

Hello everyone, I need help. (who doesn't, lol)

I am trying to build a macro to
create real estate trend charts/graphs. I need to work with different
amounts
of data every time; however, the column layout is the same everytime.
I recorded a basic marco, but it messes up when I have different
amounts
of sales to graph. *I am self taught and I need someone
to open my eyes a little further. Any help would be greatly
appreciated.

Anthony.- Hide quoted text -

- Show quoted text -

On Apr 15, 4:27*pm, Anthony0247 wrote:
Bernie,

Thank you for your advice and help. Here is the code below. If I
change the size of the data set it won't finish the macro.

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 4/13/2008 by Anthony Young
'
' Keyboard Shortcut: Ctrl+a
'
* * Columns("B:B").Select
* * Selection.Insert Shift:=xlToRight
* * Range("B1").Select
* * ActiveCell.FormulaR1C1 = "Sales Volume"
* * Range("B2").Select
* * ActiveCell.FormulaR1C1 = "1/1/1900"
* * Range("B2").Select
* * Selection.NumberFormat = "0.00"
* * Selection.NumberFormat = "0.0"
* * Selection.NumberFormat = "0"
* * Selection.AutoFill Destination:=Range("B2:B193")
* * Range("B2:B193").Select
* * Columns("D:D").Select
* * Selection.Insert Shift:=xlToRight
* * Range("C1").Select
* * ActiveCell.FormulaR1C1 = "1500-1600 SqFt Homes"
* * Range("D1").Select
* * ActiveCell.FormulaR1C1 = "1600-1700 SqFt Homes"
* * Columns("C:D").Select
* * Columns("C:D").EntireColumn.AutoFit
* * Range("A2").Select
* * ActiveWindow.FreezePanes = True
* * ActiveWindow.SmallScroll Down:=105
* * Range("C110").Select
* * Range(Selection, Selection.End(xlDown)).Select
* * Range("C164").Select
* * ActiveWindow.SmallScroll Down:=-60
* * Range("C110").Select
* * Range(Selection, Selection.End(xlDown)).Select
* * Selection.Cut Destination:=Range("D110:D193")
* * Range("D110:D193").Select
* * ActiveWindow.SmallScroll Down:=-195
* * Columns("A:A").Select
* * Range("A1:FH193").Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:= _
* * * * xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
* * * * DataOption1:=xlSortNormal
* * Range("A1").Select
* * ActiveCell.FormulaR1C1 = "DATE"
* * Range("A1:D1").Select
* * Range(Selection, Selection.End(xlDown)).Select
* * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
* * * * "Sheet1!R1C1:R193C4").CreatePivotTable TableDestination:="",
TableName:= _
* * * * "PivotTable3", DefaultVersion:=xlPivotTableVersion10
* * ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
* * ActiveSheet.Cells(3, 1).Select
* * ActiveSheet.PivotTables("PivotTable3").PivotSelect "",
xlDataAndLabel, True
* * ActiveSheet.PivotTables("PivotTable3").Format xlReport10
* * With ActiveSheet.PivotTables("PivotTable3").PivotFields ("DATE")
* * * * .Orientation = xlRowField
* * * * .Position = 1
* * End With
* * ActiveSheet.PivotTables("PivotTable3").AddDataFiel d
ActiveSheet.PivotTables( _
* * * * "PivotTable3").PivotFields("1500-1600 SqFt Homes"), _
* * * * "Count of 1500-1600 SqFt Homes", xlCount
* * ActiveSheet.PivotTables("PivotTable3").AddDataFiel d
ActiveSheet.PivotTables( _
* * * * "PivotTable3").PivotFields("1600-1700 SqFt Homes"), _
* * * * "Count of 1600-1700 SqFt Homes", xlCount
* * ActiveSheet.PivotTables("PivotTable3").AddDataFiel d
ActiveSheet.PivotTables( _
* * * * "PivotTable3").PivotFields("Sales Volume"), "Sum of Sales
Volume", xlSum
* * Range("B3").Select
* * ActiveSheet.PivotTables("PivotTable3").PivotFields ( _
* * * * "Count of 1500-1600 SqFt Homes").Function = xlAverage
* * ActiveSheet.PivotTables("PivotTable3").PivotSelect _
* * * * "'Count of 1600-1700 SqFt Homes'", xlDataAndLabel, True
* * Range("C3").Select
* * ActiveSheet.PivotTables("PivotTable3").PivotFields ( _
* * * * "Count of 1600-1700 SqFt Homes").Function = xlAverage
* * Range("A3").Select
* * Selection.Group Start:=True, End:=True, Periods:=Array(False,
False, False, _
* * * * False, False, True, True)
* * Charts.Add
* * ActiveChart.SetSourceData Source:=Sheets("Sheet4").Range("A3")
* * ActiveChart.Location Whe=xlLocationAsNewSheet
* * ActiveChart.ChartArea.Select
* * ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
* * * * "Lines on 2Axes"
* * ActiveChart.PlotArea.Select
* * With Selection.Border
* * * * .ColorIndex = 16
* * * * .Weight = xlThin
* * * * .LineStyle = xlContinuous
* * End With
* * With Selection.Interior
* * * * .ColorIndex = 2
* * * * .PatternColorIndex = 1
* * * * .Pattern = xlSolid
* * End With
* * ActiveChart.SeriesCollection(3).Select
* * ActiveChart.SeriesCollection(3).ChartType = xlColumnClustered
* * ActiveChart.Axes(xlValue, xlSecondary).Select
* * With ActiveChart.Axes(xlValue, xlSecondary)
* * * * .MinimumScaleIsAuto = True
* * * * .MaximumScale = 40
* * * * .MinorUnitIsAuto = True
* * * * .MajorUnitIsAuto = True
* * * * .Crosses = xlAutomatic
* * * * .ReversePlotOrder = False
* * * * .ScaleType = xlLinear
* * * * .DisplayUnit = xlNone
* * End With
* * ActiveChart.SeriesCollection(3).Select
* * ActiveChart.SeriesCollection(3).Trendlines.Add(Typ e:=xlPolynomial,
Order:=4 _
* * * * , Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
* * * * False).Select
* * ActiveChart.SeriesCollection(3).Trendlines(1).Sele ct
* * With Selection
* * * * .Type = xlPolynomial
* * * * .Order = 4
* * * * .Forward = 0
* * * * .Backward = 0
* * * * .InterceptIsAuto = True
* * * * .DisplayEquation = False
* * * * .DisplayRSquared = False
* * * * .Name = "Sales Volume Trend"
* * End With
* * ActiveChart.SeriesCollection(3).Select
* * With Selection.Border
* * * * .Weight = xlThin
* * * * .LineStyle = xlAutomatic
* * End With
* * Selection.Shadow = False
* * Selection.InvertIfNegative = False
* * Selection.Fill.OneColorGradient Style:=msoGradientVertical,
Variant:=3, _
* * * * Degree:=0.809796292057679
* * With Selection
* * * * .Fill.Visible = True
* * * * .Fill.ForeColor.SchemeColor = 54
* * End With
* * ActiveChart.SeriesCollection(1).Select
* * ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlPolynomial,
Order:=5 _
* * * * , Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
* * * * False).Select
* * ActiveChart.PlotArea.Select
* * ActiveChart.SeriesCollection(1).Trendlines(1).Sele ct
* * With Selection
* * * * .Type = xlPolynomial
* * * * .Order = 2
* * * * .Forward = 0
* * * * .Backward = 0
* * * * .InterceptIsAuto = True
* * * * .DisplayEquation = False
* * * * .DisplayRSquared = False
* * * * .Name = "1500-1600 SqFt Trend"
* * End With
* * ActiveChart.PlotArea.Select
* * ActiveChart.SeriesCollection(1).Trendlines(1).Sele ct
* * With Selection.Border
* * * * .ColorIndex =3
* * * * .Weight = xlThick
* * * * .LineStyle = xlContinuous
* * End With
* * ActiveChart.SeriesCollection(2).Select
* * ActiveChart.SeriesCollection(2).Trendlines.Add(Typ e:=xlPolynomial,
Order:=2 _
* * * * , Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
* * * * False, Name:="1600-1700 SqFt Trend").Select
* * ActiveChart.SeriesCollection(2).Select
* * With Selection.Border
* * * * .Weight = xlThin
* * * * .LineStyle = xlNone
* * End With
* * With Selection
* * * * .MarkerBackgroundColorIndex = xlAutomatic
* * * * .MarkerForegroundColorIndex = xlAutomatic
* * * * .MarkerStyle = xlNone
* * * * .Smooth = False
* * * * .MarkerSize = 5
* * * * .Shadow = False
* * End With
* * ActiveChart.SeriesCollection(1).Select
* * With Selection.Border
* * * * .Weight = xlThin
* * * * .LineStyle = xlNone
* * End With
* * With Selection
* * * * .MarkerBackgroundColorIndex = xlAutomatic
* * * * .MarkerForegroundColorIndex = xlAutomatic
* * * * .MarkerStyle = xlNone
* * * * .Smooth = False
* * * * .MarkerSize = 5
* * * * .Shadow = False
* * End With
* * ActiveChart.PlotArea.Select
* * ActiveChart.SeriesCollection(2).Trendlines(1).Sele ct
* * With Selection.Border
* * * * .ColorIndex = 5
* * * * .Weight = xlThick
* * * * .LineStyle = xlContinuous
* * End With
* * ActiveChart.PlotArea.Select
* * ActiveChart.Legend.Select
* * ActiveChart.Legend.LegendEntries(2).Select
* * Selection.Delete
* * ActiveChart.Legend.Select
* * ActiveChart.Legend.LegendEntries(2).Select
* * Selection.Delete
* * With ActiveChart
* * * * .HasTitle = True
* * * * .ChartTitle.Characters.Text = "Sales Volume and Price Trend"
* * * * .Axes(xlCategory, xlPrimary).HasTitle = True
* * * * .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "DATE/
QUARTER"
* * * * .Axes(xlValue, xlPrimary).HasTitle = True
* * * * .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales
Price"
* * * * .Axes(xlValue, xlSecondary).HasTitle = True
* * * * .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Sales
Volume"
* * End With
* * With ActiveChart.Axes(xlCategory)
* * * * .HasMajorGridlines = False
* * * * .HasMinorGridlines = False
* * End With
* * With ActiveChart.Axes(xlValue)
* * * * .HasMajorGridlines = True
* * * * .HasMinorGridlines = False
* * End With
* * ActiveChart.Axes(xlValue).AxisTitle.Select
* * Selection.AutoScaleFont = False
* * With Selection.Font
* * * * .Name = "Arial"
* * * * .Size = 18
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * * * .ColorIndex = xlAutomatic
* * * * .Background = xlAutomatic
* * End With
* * ActiveChart.Axes(xlCategory).AxisTitle.Select
* * Selection.AutoScaleFont = False
* * With Selection.Font
* * * * .Name = "Arial"
* * * * .Size = 20
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * * * .ColorIndex = xlAutomatic
* * * * .Background = xlAutomatic
* * End With
* * ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
* * Selection.AutoScaleFont = False
* * With Selection.Font
* * * * .Name = "Arial"
* * * * .Size = 18
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * * * .ColorIndex = xlAutomatic
* * * * .Background = xlAutomatic
* * End With
* * ActiveChart.ChartArea.Select
* * ActiveChart.ChartTitle.Select
* * Selection.AutoScaleFont = False
* * With Selection.Font
* * * * .Name = "Arial"
* * * * .Size = 20
* * * * .Strikethrough = False
* * * * .Superscript = False
* * * * .Subscript = False
* * * * .OutlineFont = False
* * * * .Shadow = False
* * * * .Underline = xlUnderlineStyleNone
* * * * .ColorIndex = xlAutomatic
* * * * .Background = xlAutomatic
* * End With
* * ActiveChart.ChartArea.Select
End Sub

On Apr 15, 9:38*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Anthony,

It would help if you posted your code.

But, generally, if you get code like

Range("A2:D10")

you can change that to

Range("A2:D" & Cells(Rows.Count,4).End(xlUp).Row)

OR

Range("A2").CurrentRegion * *(but this depends on layout with headers etc)

OR

Range("A2", Range("A2").End(xlDown).End(xlToRight))

The best solution depends on your layout.

HTH,
Bernie
MS Excel MVP

"Anthony0247" wrote in message

...

Hello everyone, I need help. (who doesn't, lol)

I am trying to build a macro to
create real estate trend charts/graphs. I need to work with different
amounts
of data every time; however, the column layout is the same everytime.
I recorded a basic marco, but it messes up when I have different
amounts
of sales to graph. *I am self taught and I need someone
to open my eyes a little further. Any help would be greatly
appreciated.

Anthony.- Hide quoted text -

- Show quoted text -

Try EZplot for Excel to quickly update data for many plots based on
parameter names. A demo version is available at www.OfficeExpander.com
Cheers!