Copy/Pasting Looping from bottom of Column Upward

basepopeball

New Member
Joined
Feb 26, 2019
Messages
3
First time posting on MrExcel. Been using forums for years and this has taught me a bunch about VBA coding. I have a project where I'm trying to loop through column A starting from the last cell with contents in it. I would like for the vba to loop upward repeating that cell value until new value is seen and continue until the top of the workbook. However, I would like it to end at Row 5 rather than 1. The code I've found goes top/down, hoping to get code to start at bottom of list and loop its' way up until row 5. Range will vary but will be in the sphere of 20,000 to 30,000 lines.

Row Column A Results
5 A
6 A
7 A A
8 B
9 B B
10 C C
11 D
12 D
13 D
14 D
15 D D


Code:
<code class="vb keyword">Sub</code> <code class="vb plain">FillDown()</code>
<code class="vb keyword">Dim</code> <code class="vb plain">xRng </code><code class="vb keyword">As</code> <code class="vb plain">Range</code>
<code class="vb keyword">Dim</code> <code class="vb plain">xRows </code><code class="vb keyword">As</code> <code class="vb keyword">Long</code><code class="vb plain">, xCols </code><code class="vb keyword">As</code> <code class="vb keyword">Long</code>
<code class="vb keyword">Dim</code> <code class="vb plain">xRow </code><code class="vb keyword">As</code> <code class="vb keyword">Integer</code><code class="vb plain">, xCol </code><code class="vb keyword">As</code> <code class="vb keyword">Integer</code>
<code class="vb keyword">Set</code> <code class="vb plain">xRng = Selection</code>
<code class="vb plain">xCols = xRng.Columns.CountLarge</code>
<code class="vb plain">xRows = xRng.Rows.CountLarge</code>
<code class="vb keyword">For</code> <code class="vb plain">xCol = 1 </code><code class="vb keyword">To</code> <code class="vb plain">xCols</code>
<code class="vb spaces">  </code><code class="vb keyword">For</code> <code class="vb plain">xRow = 1 </code><code class="vb keyword">To</code> <code class="vb plain">xRows - 1</code>
<code class="vb spaces">    </code><code class="vb keyword">If</code> <code class="vb plain">xRng.Cells(xRow, xCol) <> </code><code class="vb string">""</code> <code class="vb keyword">Then</code>
<code class="vb spaces">      </code><code class="vb plain">xRng.Cells(xRow, xCol) = xRng.Cells(xRow, xCol).Value</code>
<code class="vb spaces">      </code><code class="vb keyword">If</code> <code class="vb plain">xRng.Cells(xRow + 1, xCol) = </code><code class="vb string">""</code> <code class="vb keyword">Then</code>
<code class="vb spaces">        </code><code class="vb plain">xRng.Cells(xRow + 1, xCol) = xRng.Cells(xRow, xCol).Value</code>
<code class="vb spaces">      </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb spaces">    </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb spaces">  </code><code class="vb keyword">Next</code> <code class="vb plain">xRow</code>
<code class="vb keyword">Next</code> <code class="vb plain">xCol</code>
<code class="vb keyword">End</code> <code class="vb keyword">Sub
</code>
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

basepopeball

New Member
Joined
Feb 26, 2019
Messages
3
Row
Column A
Result
5
A
6
A
7
A
A
8
B
9
B
B
10
C
C
11
D
12
D
13
D
14
D
15
D
D

<tbody>
</tbody>

Added actual table for reference
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
Try this:

Code:
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1088978b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1088978-copy-pasting-looping-bottom-column-upward.html[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va

va = Range([COLOR=brown]"A5"[/COLOR], Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))

    [COLOR=Royalblue]For[/COLOR] i = UBound(va, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Step[/COLOR] -[COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]If[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])
    [COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"B5"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = va
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]
 

basepopeball

New Member
Joined
Feb 26, 2019
Messages
3
Your amazing this works perfectly. Thank you so much for this. Saved immense amount of pain and struggle.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,879
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top