VBA to fill in the gaps?

[MichaelRSnow]

Afternoon All

Anyone know if you can get VBA to fill in the gaps based on the entry above, for example, A1 'Rate1' is copied down until it hits A5 'Rate2', then Rate2 is copied down until it hits A7 'Rate3' etc, End result is it starts off looking like table to the left and end up looking like table to the right?

	A	B	C			A	B	C
1	Rate1	TypeA	Name1		1	Rate1	TypeA	Name1
2			Name2		2	Rate1	TypeA	Name2
3		TypeB	Name1		3	Rate1	TypeB	Name1
4			Name2		4	Rate1	TypeB	Name2
5	Rate2	TypeA	Name1		5	Rate2	TypeA	Name1
6			Name2		6	Rate2	TypeA	Name2
7	Rate3	TypeA	Name1		7	Rate3	TypeA	Name1
8			Name2		8	Rate3	TypeA	Name2
9		TypeB	Name1		9	Rate3	TypeB	Name1
10			Name2		10	Rate3	TypeB	Name2
11			Name3		11	Rate3	TypeB	Name3
12		TypeC	Name1		12	Rate3	TypeC	Name1
13			Name2		13	Rate3	TypeC	Name2
14	Rate4	TypeB	Name1		14	Rate4	TypeB	Name1
15			Name2		15	Rate4	TypeB	Name2
16			Name3		16	Rate4	TypeB	Name3
17			Name4		17	Rate4	TypeB	Name4
18		TypeC	Name1		18	Rate4	TypeC	Name1
19	Rate5	TypeA	Name1		19	Rate5	TypeA	Name1
20			Name2		20	Rate5	TypeA	Name2

<COLGROUP><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" width=26><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

 

[Ron de Bruin]

Why VBA ?

Select the A column
F5>Special
Blanks
Enter a = sign
press the arrow up key and hit Ctrl Enter

 

[Ron de Bruin]

For VBA see this page
http://www.contextures.com/xlDataEntry02.html

 

[mirabeau]

Afternoon All

Anyone know if you can get VBA to fill in the gaps based on the entry above, for example, A1 'Rate1' is copied down until it hits A5 'Rate2', then Rate2 is copied down until it hits A7 'Rate3' etc, End result is it starts off looking like table to the left and end up looking like table to the right?
...

Since you asked about VBA, here's a VBA approach as applied to ColumnA

Sub fillgaps()
Dim e
For Each e In Range("A:A")
If Len(e(2)) = 0 Then e(2) = e.Value
If e(2).End(4).Row = Rows.Count Then Exit For
Next e
End Sub

So yes, if you wish you can do it with VBA.

 

[MichaelRSnow]

Since you asked about VBA, here's a VBA approach as applied to ColumnA

Sub fillgaps()
Dim e
For Each e In Range("A:A")
If Len(e(2)) = 0 Then e(2) = e.Value
If e(2).End(4).Row = Rows.Count Then Exit For
Next e
End Sub

So yes, if you wish you can do it with VBA.

Thanks mirabeau, does is exactly what I need it to do.

 

[mirabeau]

Thanks mirabeau, does is exactly what I need it to do.

Thanks for feedback.

Just for the record, here's another, faster one. Took me under 1.2 seconds to fill in randomized gaps in 1 million rows. (where 50% of cells were gaps)

Sub morefillgaps()
Dim lr As Long, i As Long, a, c
lr = Range("A" & Rows.Count).End(3).Row
a = Range("A1").Resize(lr)
For i = 1 To lr
    If Len(a(i, 1)) > 0 Then c = a(i, 1) Else a(i, 1) = c
Next i
Range("A1").Resize(lr) = a
End Sub

 

[MichaelRSnow]

Thanks for feedback.

Just for the record, here's another, faster one. Took me under 1.2 seconds to fill in randomized gaps in 1 million rows. (where 50% of cells were gaps)

Sub morefillgaps()
Dim lr As Long, i As Long, a, c
lr = Range("A" & Rows.Count).End(3).Row
a = Range("A1").Resize(lr)
For i = 1 To lr
    If Len(a(i, 1)) > 0 Then c = a(i, 1) Else a(i, 1) = c
Next i
Range("A1").Resize(lr) = a
End Sub

Thanks mate, mine is 45,000 rows A to AD so the previous version did this in a few seconds also. thanks again

 

[alexvsince92]

Since you asked about VBA, here's a VBA approach as applied to ColumnA

Sub fillgaps()
Dim e
For Each e In Range("A:A")
If Len(e(2)) = 0 Then e(2) = e.Value
If e(2).End(4).Row = Rows.Count Then Exit For
Next e
End Sub

So yes, if you wish you can do it with VBA.

this works perfect, i use it for two columns. Column A stops at the end of my range, column B doesnt. Any help?

 

[Joe4]

this works perfect, i use it for two columns. Column A stops at the end of my range, column B doesnt. Any help?

Welcome to the Board!

You would be better off posting your question to a new thread instead of reviving a thread that is over 10 years old, and none of the people on it are active on this board anymore.
Then it will also appear on the "Unanswered threads" listing that many people use to look for new unanswered questions.