Code frustration...please help

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
1,075
Office Version
  1. 2019
Platform
  1. Windows
Code:
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
Code:
Set rData = Range("A18", Range("B" & Rows.Count).End(xlUp))

Thank you kindly
 
Last edited:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
The code seems not consistent or stable... I get this output sometimes..

[TABLE="width: 509"]
<tbody>[TR]
[TD]-16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item[/TD]
[TD]Ticker[/TD]
[TD]Company[/TD]
[TD] Amount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]BA[/TD]
[TD]The Boeing Company[/TD]
[TD] $ 3,410.26[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AAP[/TD]
[TD]Advance Auto Parts[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD] $ (2,861.56)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ABT[/TD]
[TD]Abbott Laboratories[/TD]
[TD] $ 1,583.74[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ADBE[/TD]
[TD]Adobe Inc.[/TD]
[TD] $ (4,105.00)[/TD]
[/TR]
</tbody>[/TABLE]


this too...

[TABLE="width: 509"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]84[/TD]
[TD]WP[/TD]
[TD]Worldpay[/TD]
[TD] $ 543.20[/TD]
[/TR]
[TR]
[TD]85[/TD]
[TD]WYNN[/TD]
[TD]Wynn Resorts[/TD]
[TD] $ 1,867.40[/TD]
[/TR]
[TR]
[TD]86[/TD]
[TD]XLNX[/TD]
[TD]Xilinx[/TD]
[TD] $ 532.10[/TD]
[/TR]
[TR]
[TD]87[/TD]
[TD]Z[/TD]
[TD]Zillow[/TD]
[TD] $ (141.90)[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]91[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]93[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]94[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]96[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]97[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]98[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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
 
Upvote 0
I understand, however its adding an additional 18 numbers with there is nothing past the last row in W
 
Upvote 0
from your original code ? or did you try the change
 
Upvote 0
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:
Code:
LRow = Range("W" & Rows.Count).End(xlUp).Row
will put the number 22 into LRow
this line of code
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,276
Messages
6,171,139
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top