Conditional Auto Fill in

[Guest]

I'm using the auto fill in macro written by Dave Peterson that was on the
contextures.com site. It works great for most things I want to do. One
thing that I would like to be able to do is have it work so that it only
fills in a row if there is something in a cell to the right. It can be in
the next cell or in the cell 3 columns over. The reason i need to do this is
that I'm trying to re-create a tree structrue coming out of another program
that leaves blanks in the export.

It comes out looking like this
Trees Oak
Evergreen Shrubs
Pine Tree Scrub Pine

Christmas Tree
(Empty Cells)
Flowers (etc)

I want the result to be
Trees Oak
Trees Evergreen Shrubs
Trees Evergreen Pine Tree Scrub Pine
Trees Evergreen Pine Tree Christmas Tree
(Empty Cells)
Flowers (etc)

I'm sure this is possible, I'm just not as familliar with Excel programming
to make it work.

Thanks
Brian

 

[Tom Ogilvy]

Assume you are going to select a rectangular area that you want filled in
and the existing cells have values, not formulas

try this on a copy of your data:

Sub HIJ()
Dim rng As Range, col as Range
Dim ar as Range, cell as Range, cell1 as Range

Set rng = Selection.SpecialCells(xlConstants)
Set rng = Intersect(rng.EntireRow, rng.EntireColumn)
rng.Select
For Each ar In rng.Areas
For Each col In ar.Columns
Set col1 = col.SpecialCells(xlBlanks)
For Each cell In col1
Set cell1 = cell.End(xlToRight)
If Not Intersect(cell1, ar) Is Nothing Then
cell.Value = cell.Offset(-1, 0)
End If
Next
Next
Next
End Sub

 

[Dave Peterson]

Debra Dalgleish has some techniques (manual and VBA):
http://contextures.com/xlDataEntry02.html

 

[Guest]

It works perfectly.

Thanks

Brian

Assume you are going to select a rectangular area that you want filled in
and the existing cells have values, not formulas

try this on a copy of your data:

Sub HIJ()
Dim rng As Range, col as Range
Dim ar as Range, cell as Range, cell1 as Range

Set rng = Selection.SpecialCells(xlConstants)
Set rng = Intersect(rng.EntireRow, rng.EntireColumn)
rng.Select
For Each ar In rng.Areas
For Each col In ar.Columns
Set col1 = col.SpecialCells(xlBlanks)
For Each cell In col1
Set cell1 = cell.End(xlToRight)
If Not Intersect(cell1, ar) Is Nothing Then
cell.Value = cell.Offset(-1, 0)
End If
Next
Next
Next
End Sub

 

[Tom Ogilvy]

I'm using the auto fill in macro written by Dave Peterson that was on the
contextures.com site.

But he says it doesn't fill the bill for this one.

 

[Dave Peterson]

I sometimes skip paragraphs, too!

But I'm glad you gave him a solution that works.