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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
288
StephenCrump
S
Balcomie
  • Solved
Formula For Gematria
Replies
3
Views
80
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
490
Anthony47
A
M
  • Question Question
I need help with VBA/macro script to collect user input and fill cells
Replies
4
Views
583
mumps
mumps
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
128
Kaper
Kaper

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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