Fill Down Macro with Varying Rows and Columns

[x33166]

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:

Code	Name	rate	Total
a	tim	1.05	2
			3
			4
	bob		5
			6
		2.01	7
b			8
			9
			10
	jim		11
			12
			13
	chris	3.552	14
			15
			16
			17
d			18
			19
			20
			21
		7	22
			23
	Ralph		24
			25
			26
			27
			28
			29
			30
			31
			32
c			33
			34
			35
v			36
			37
			38
			39
			40
			41
			42
			43
x			44
			45
			46

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

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:

Code	Name	rate	Total
a	tim	1.05	2
a	tim	1.05	3
a	tim	1.05	4
a	bob	1.05	5
a	bob	1.05	6
a	bob	2.01	7
b	bob	2.01	8
b	bob	2.01	9
b	bob	2.01	10
b	jim	2.01	11
b	jim	2.01	12
b	jim	2.01	13
b	chris	3.552	14
b	chris	3.552	15
b	chris	3.552	16
b	chris	3.552	17
d	chris	3.552	18
d	chris	3.552	19
d	chris	3.552	20
d	chris	3.552	21
d	chris	7	22
d	chris	7	23
d	Ralph	7	24
d	Ralph	7	25
d	Ralph	7	26
d	Ralph	7	27
d	Ralph	7	28
d	Ralph	7	29
d	Ralph	7	30
d	Ralph	7	31
d	Ralph	7	32
c	Ralph	7	33
c	Ralph	7	34
c	Ralph	7	35
v	Ralph	7	36
v	Ralph	7	37
v	Ralph	7	38
v	Ralph	7	39
v	Ralph	7	40
v	Ralph	7	41
v	Ralph	7	42
v	Ralph	7	43
x	Ralph	7	44
x	Ralph	7	45
x	Ralph	7	46

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

I hope this all makes sense. Appreciate any assistance

 

[Fluff]

Hi & welcome to MrExcel.
How about

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

 

[x33166]

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.

 

[Fluff]

Ok, how about

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

 

[x33166]

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.

 

[Fluff]

Glad to help & thanks for the feedback