Trying to select multiple last rows, but I am not sure how to do this in VBA.
I've tried two different methods
Run TIME ERROR '438' Object does not support this method
Run TIME ERROR '450' Wrong number of arguments or invalid property assignment
I've tried two different methods
Run TIME ERROR '438' Object does not support this method
Run TIME ERROR '450' Wrong number of arguments or invalid property assignment
Code:
Sub DiffLondon()
Dim ws As Sheets
Dim Wkb As Excel.Workbook
Dim i As Long
Dim LastRow As Long
Dim ws_count As Long
[B]Dim l As Range[/B]
[B]Dim n As Range[/B]
[B]Dim p As Range[/B]
Set Wkb = ThisWorkbook
Set ws = ThisWorkbook.Worksheets
ws_count = Wkb.Worksheets.Count
'Begin loop
For i = 5 To ws_count
LastRow = Wkb.Worksheets(i).Cells(Rows.Count, 3).End(xlUp).Row
[B] Wkb.Sheets(i).Activate[/B]
[B] Set l = ws(i).Range("l43:l" & LastRow)[/B]
[B] Set n = ws(i).Range("n43:n" & LastRow)[/B]
[B] Set p = ws(i).Range("p43:p" & LastRow)[/B]
[B] ActiveSheet.Union(l, n, p).Select[/B]
[B] ActiveSheet.Range("l43:l" & LastRow, "n43:n" & LastRow, "p43:p" & LastRow).Select[/B]
ws(i).Shapes.AddChart(xlColumnClustered, _
Left:=ws(i).Range("r88").Left, _
Top:=ws(i).Range("aj88").Top, _
Width:=ws(i).Range("r88:aj88").Width, _
Height:=ws(i).Range("r88:aj134").Height).Select
With ActiveChart
'Title
.HasTitle = True
.ChartTitle.Characters.Text = Range("A2")
'Primary Axis
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Citi Fix"
.Axes(xlValue, xlPrimary).HasTitle = False
'Series 2 WMR - Citi Fix
With .SeriesCollection(1)
.ChartType = xlColumnClustered
.AxisGroup = 1
.Name = "WMR"
.XValues = Range("l43:l" & LastRow)
.Interior.Color = xlNone
.Border.Weight = 1.5
.Border.Color = RGB(192, 80, 77)
End With
.SeriesCollection(1).XValues = ws(i).Range("f43:f" & LastRow)
'Series 3 B Fix - Citi Fix
With .SeriesCollection(2)
.ChartType = xlColumnClustered
.AxisGroup = 2
.Name = "B Fix"
.XValues = Range("n43:n" & LastRow)
.Border.Weight = 1
.Interior.Color = RGB(79, 129, 189)
End With
'SecondaryAxis
With ActiveChart
.Axes(xlValue, xlSecondary).MaximumScale = .Axes(xlValue).MaximumScale
.Axes(xlValue, xlSecondary).MinimumScale = .Axes(xlValue).MinimumScale
End With
With ActiveChart.Axes(xlValue)
With .Border
.Weight = xlHairline
.LineStyle = xlNone
End With
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNone
End With
'Series 4 Average Horizontal Line
With .SeriesCollection(3)
.ChartType = xlLine
.AxisGroup = 1
.Name = "Average"
.XValues = Range("p43:p" & LastRow)
.Border.Weight = 1
.Border.LineStyle = xlDot
.Border.Color = RGB(0, 0, 0)
End With
'Axis Title
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
End With
Next
End Sub
