Fill Down Macro with Varying Rows and Columns

X

x33166

New Member
Joined
Aug 20, 2018
Messages
3
I've looked everywhere and maybe I just wasn't wording my search properly but I cannot find a solution to a problem I have on a daily basis. I'm looking for a macro that will do the following:


[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Code[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]rate[/TD]
[TD="width: 64"]Total[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]tim[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]bob[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.01[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]jim[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]chris[/TD]
[TD="align: right"]3.552[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ralph[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
The above sample data set (each time the number of rows and columns will be different but the last column will always have a value in every row so the last column should be used to stop the fill down and row 1 will always be a header row so start fill down with data in row 2) I would like the solution to look like:

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Code[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]rate[/TD]
[TD="width: 64"]Total[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]tim[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]tim[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]tim[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]bob[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]bob[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]bob[/TD]
[TD="align: right"]2.01[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]bob[/TD]
[TD="align: right"]2.01[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]bob[/TD]
[TD="align: right"]2.01[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]bob[/TD]
[TD="align: right"]2.01[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]jim[/TD]
[TD="align: right"]2.01[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]jim[/TD]
[TD="align: right"]2.01[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]jim[/TD]
[TD="align: right"]2.01[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]chris[/TD]
[TD="align: right"]3.552[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]chris[/TD]
[TD="align: right"]3.552[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]chris[/TD]
[TD="align: right"]3.552[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]chris[/TD]
[TD="align: right"]3.552[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]chris[/TD]
[TD="align: right"]3.552[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]chris[/TD]
[TD="align: right"]3.552[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]chris[/TD]
[TD="align: right"]3.552[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]chris[/TD]
[TD="align: right"]3.552[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]chris[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]chris[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Ralph[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]46

[/TD]
[/TR]
</tbody>[/TABLE]

I hope this all makes sense. Appreciate any assistance
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about
Code: 
Sub Fillme()
   With Range("A3", Range("D" & Rows.count).End(xlUp).Offset(, -1))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub
 
Upvote 0
Thanks for your quick reply. It works if column D is my last column but that won't always be the case. Each data set will vary by number of rows and columns. So column might be the last column or it could be column AB.
 
Upvote 0
Ok, how about
Code: 
Sub Fillme()
   Dim Lc As Long
   Lc = Cells(1, Columns.count).End(xlToLeft).Column
   With Range("A3", Cells(Rows.count, Lc).End(xlUp).Offset(, -1))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub
 
Upvote 0
AWESOME!!!! Thank you so much and again for responding so quickly. You have no idea how much time you're going to save me. I will be using this daily.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Solved
ADDING 2ND COLUMN TO ARRAY, THEN ONLY OUTPUTTING THE SECOND COLUMN
Replies
3
Views
278
StephenCrump
S
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
442
Anthony47
A
M
  • Question Question
I need help with VBA/macro script to collect user input and fill cells
Replies
4
Views
505
mumps
mumps
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
116
Kaper
Kaper
Z
  • Question Question
Delete Rows not needed Macro
2
Replies
12
Views
317
zone709
Z

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

Share this page

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