Conditional Auto Fill in
 

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

Conditional Auto Fill in
 

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

--
Regards,
Tom Ogilvy


"Brian" wrote in message
...
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

Conditional Auto Fill in
 

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

Brian wrote:

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

--

Dave Peterson

Conditional Auto Fill in
 

It works perfectly.

Thanks

Brian

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Brian" wrote in message
...
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

Conditional Auto Fill in
 

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.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Debra Dalgleish has some techniques (manual and VBA):
http://contextures.com/xlDataEntry02.html

Brian wrote:

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

--

Dave Peterson

Conditional Auto Fill in
 

I sometimes skip paragraphs, too!

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



Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Debra Dalgleish has some techniques (manual and VBA):
http://contextures.com/xlDataEntry02.html

Brian wrote:

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

--

Dave Peterson

--

Dave Peterson