Conditional Auto Fill in

[Brian]

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

--
Regards,
Tom Ogilvy


"Brian" <Brian@discussions.microsoft.com> wrote in message
news:2C69F533-952C-494B-B29A-711554AD5197@microsoft.com...
> 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]

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

[Brian]

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" <Brian@discussions.microsoft.com> wrote in message
> news:2C69F533-952C-494B-B29A-711554AD5197@microsoft.com...
> > 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]

> 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" <petersod@verizonXSPAM.net> wrote in message
news:436B79BE.8F1B0F0@verizonXSPAM.net...
> 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]

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" <petersod@verizonXSPAM.net> wrote in message
> news:436B79BE.8F1B0F0@verizonXSPAM.net...
> > 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