Fill Down VBA help please

[simonlavender]

Imagine in Excel you have A1 = 1

A6 = 2

A11 = 3

This is for this weeks data, next week value 2 could be in A7 or A10
or A12 etc.

I want to have the flexibility to fill down from e.g. this week from
A1 to A5 and fill A6 to A10 will a 2 etc.

How can I do this? I can't seem to get the range flexibility, thanks.

 

[Don Guillett]

Try this idea
Sub fillnumbersmod()
j = 1
For i = 1 To 12 Step 5
Cells(i, "a").Resize(5) = j
j = j + 1
Next i
End Sub

 

[Tom Ogilvy]

One interpretation

Assume the first value is in A1 and other values across the range A1:A500
and you want to stop filling at A500.


Sub fillcells()
Dim rng As Range
Range("A501").Value = "A"
Set rng = Range("A1:A500").SpecialCells(xlBlanks)
rng.Formula = "=A1"
Range("A1:A500").Value = Range("A1:A500").Value
Range("A501").EntireRow.Delete
End Sub

the commands for A501 are there to insure the UsedRange of the sheet extends
at least as far as you want to fill since specialcells doesn't work past the
end of the UsedRange.

 

[Simon]

Hi Don,

The gap from where "1", typically is in A6, yet the location for "2"
could be anywhere below , e.g. in A7, A9 , A20 etc.

After A6 the cells below are blank until there is a value of 2 in
whichever cell it is.

However If(IsBlank) works within Excel via the addition of a column
yet I wish to "hardwire" this in VB. Any suggestions?

Regards,

Simon

 

[Simon]

Hi Tom,


Thansk for your reply.


The gap from where "1", is typically in A6, yet the location for "2"
could be anywhere below , e.g. in A7, A9 , A20 etc.

After A6 the cells below are blank until there is a value of 2 in
whichever cell it is.

However If(IsBlank) works within Excel via the addition of a column
yet I wish to "hardwire" this in VB. Any suggestions?
For example (1 uis normally static - actually in A6), the rest of the
cells below are blank, due to a crosstab, until 2 occurs in a cell,
then below the cells are blank until 3, etc, down to 6 currently. The
reason for filling int he blanks is so that a filter can be put in
place:


A1: 1
A2:
A3:
A4:
A5:
A6:
A7: 2
A8:
A9:
A10:
A11:
A12:
A13:
A14 3