I have a data extract from a crosstab and now on a regular basis need
to fill in the blanks to apply a suitable filter.

Below I've included an example.

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

This will continue until 6 is reached and then I will need something
(xlUp?) to obtain the LastRow to stop filling down to 65536.

The ranges are not static and will vary. I've attempted FillDown but
I've come across issues keeping the ranges flexible.

THe data is typically pasted into A6, but that is about all that is
rigid from a point of view of using a suitable ActiveCell range.

A6 is where 1

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?

Try this

Sub fill()

LastRow = ActiveSheet.Cells. _
SpecialCells(xlCellTypeLastCell).Row
'find first data
FirstRow = 1
Do While Range("A" & FirstRow) = ""
FirstRow = FirstRow + 1
Loop
Copydata = Range("A" & FirstRow)

For RowCount = (FirstRow + 1) To LastRow
If Range("A" & RowCount) = "" Then
Range("A" & RowCount) = Copydata
Else
Copydata = Range("A" & RowCount)
End If
Next RowCount
End Sub
"Simon" wrote:

I have a data extract from a crosstab and now on a regular basis need
to fill in the blanks to apply a suitable filter.

Below I've included an example.

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

This will continue until 6 is reached and then I will need something
(xlUp?) to obtain the LastRow to stop filling down to 65536.

The ranges are not static and will vary. I've attempted FillDown but
I've come across issues keeping the ranges flexible.

THe data is typically pasted into A6, but that is about all that is
rigid from a point of view of using a suitable ActiveCell range.

A6 is where 1

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?

Sub FillData()
With ActiveSheet
Set r = Intersect(.Columns(1), .UsedRange)
End With
Set r1 = r.SpecialCells(xlBlanks)
r1.Formula = "=A1"
r.Copy
r.PasteSpecial xlValues
End Sub

--
Regards,
Tom Ogilvy


"Simon" wrote:

I have a data extract from a crosstab and now on a regular basis need
to fill in the blanks to apply a suitable filter.

Below I've included an example.

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

This will continue until 6 is reached and then I will need something
(xlUp?) to obtain the LastRow to stop filling down to 65536.

The ranges are not static and will vary. I've attempted FillDown but
I've come across issues keeping the ranges flexible.

THe data is typically pasted into A6, but that is about all that is
rigid from a point of view of using a suitable ActiveCell range.

A6 is where 1

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?

Debra Dalgleish has manual and mechanized suggestions he
http://contextures.com/xlDataEntry02.html

If it's not part of a larger routine, I'd do it manually. I find it quicker
than finding the macro and running it.

The manual method in video:
http://www.contextures.com/xlVideos01.html#FillBlanks


Simon wrote:

I have a data extract from a crosstab and now on a regular basis need
to fill in the blanks to apply a suitable filter.

Below I've included an example.

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

This will continue until 6 is reached and then I will need something
(xlUp?) to obtain the LastRow to stop filling down to 65536.

The ranges are not static and will vary. I've attempted FillDown but
I've come across issues keeping the ranges flexible.

THe data is typically pasted into A6, but that is about all that is
rigid from a point of view of using a suitable ActiveCell range.

A6 is where 1

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?

--

Dave Peterson