Code frustration...please help

[Av8tordude]

Dim rData As RangeDim Lst As Long, LRow As Long


LRow = Range("W" & Rows.Count).End(xlUp).Row
Range("V18:Y" & LRow).ClearContents


Set rData = Range("A18", Range("B" & Rows.Count).End(xlUp))
rData.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("W18"), Unique:=True


With Range("W18").CurrentRegion
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes
    .Columns(3).Formula = "=SUMIF(" & rData.Columns(1).Address & ",W18," & rData.Columns(16).Address & ")"
    Range("V18") = 1
    Range("V18").AutoFill Destination:=Range("V18").Resize(LRow), Type:=xlFillSeries
End With

I'm trying to autofill numerical numbers to the last used row in column W, but the autofill fills in an additional 16 numbers past the last row in column W. Please help fix my dilemma

Also I set my table to start from Row 18 and I adjusted this part of the code, but I'm not sure I understand why I can't adjust Range("B" & Rows.Count to reflect it to start from row 18

Set rData = Range("A18", Range("B" & Rows.Count).End(xlUp))

Thank you kindly

 

[mole999]

when you F8 with the code does lastrow change at all
or you do something along these lines
Range("V18").AutoFill Destination:=Range("V18").Resize(LRow), Type:=xlFillSeries
Range("V18").AutoFill Destination:=Range("V18:V" & LRow), Type:=xlFillSeries

 

[Av8tordude]

when you F8 with the code does lastrow change at all

I'm not sure what you mean, but A18 to lastrow is dynamic so there will be more items added.

or you do something along these lines
Range("V18").AutoFill Destination:=Range("V18").Resize(LRow), Type:=xlFillSeries
Range("V18").AutoFill Destination:=Range("V18:V" & LRow), Type:=xlFillSeries

I'm not sure what you mean by this.

 

[Av8tordude]

The code seems not consistent or stable... I get this output sometimes..

-16
-15
-14
-13
-12
-11
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
Item	Ticker	Company	Amount
1	BA	The Boeing Company	$ 3,410.26
	AAP	Advance Auto Parts	$ -
	AAPL	Apple Inc.	$ (2,861.56)
	ABT	Abbott Laboratories	$ 1,583.74
	ADBE	Adobe Inc.	$ (4,105.00)

<tbody>
</tbody>

this too...

84	WP	Worldpay	$ 543.20
85	WYNN	Wynn Resorts	$ 1,867.40
86	XLNX	Xilinx	$ 532.10
87	Z	Zillow	$ (141.90)
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[mole999]

So this >
LRow = Range("W" & Rows.Count).End(xlUp).Row providing W is the Lowest Row should give you an end point

Range("V18").AutoFill Destination:=Range("V18:V" & LRow), Type:=xlFillSeries should fill V down to the LRow Value much like (V18:V45) would

 

[Av8tordude]

I understand, however its adding an additional 18 numbers with there is nothing past the last row in W

 

[mole999]

from your original code ? or did you try the change

 

[offthelip]

Perhaps an explanation as to why you are getting an extra 18 rows will help:
Assume the last line with data is row 22
this line:

LRow = Range("W" & Rows.Count).End(xlUp).Row

will put the number 22 into LRow
this line of code

Range("V18").AutoFill Destination:=Range("V18").Resize(LRow), Type:=xlFillSeries

says fiill a range starting a V18 and resize by Lrow (which is 22), which put s the end of the range at row 40, i.e. 18 rows beyond where you need it.