Selectdown Macro w/ varying rows
 

Hi Tom

SpecialCells can only handle 8192 Areas
And not changed in 2007, stupid with the large grid.


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Tom Ogilvy" wrote in message
...
If the cells contain constants and not formulas:

Sub FillInData()
Dim rng as Range, rng1 as Range
Set rng = Range(Cells(1, ActiveCell.Column), _
Cells(Rows.Count, ActiveCell.Column).End(xlUp))
Set rng1 = rng.SpecialCells(xlBlanks)
rng1.Formula = "=R[-1]C"
rng.Formula = rng.Value
End Sub

SpecialCells can only handle 8192 Areas - I haven't added any code to
account for that as you sample is well below that limit.

--
Regards,
Tom Ogilvy


"cmcgue" wrote:

How do I copy down a set of data in a column when there are varying
spaces
between the rows? I can do this with the following:

For x = 1 To 12
Range(ActiveCell, ActiveCell.End(xlDown).Offset(-1, 0)).Select
Selection.FillDown
ActiveCell.End(xlDown).Select
Next x

This accomplishes the select down copy cells that I need but only if I
know
the number of times to execute. How do you do obtain the same results
when
you don't know the number of times to execute? I want to go to end of the
column until there is no data remaining and I want to account for
instances
where there are consecutive rows.

Selectdown Macro w/ varying rows
 

Areas not rows

See this page for a check you can use to count the areas
http://www.rondebruin.nl/specialcells.htm

See the workeround on the bottom of the page


--

Regards Ron de Bruin
http://www.rondebruin.nl



"cmcgue" wrote in message
...
Thank You Professor Olgivy; Works like a charm! Do you teach coding
concepts
in VB?

Also, just to clarify - Can I only use this code with 8192 rows or less?
Is
there a way around it?

P.S. Thanks for your comment Ron.

"Ron de Bruin" wrote:

Hi Tom

SpecialCells can only handle 8192 Areas
And not changed in 2007, stupid with the large grid.


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Tom Ogilvy" wrote in message
...
If the cells contain constants and not formulas:

Sub FillInData()
Dim rng as Range, rng1 as Range
Set rng = Range(Cells(1, ActiveCell.Column), _
Cells(Rows.Count, ActiveCell.Column).End(xlUp))
Set rng1 = rng.SpecialCells(xlBlanks)
rng1.Formula = "=R[-1]C"
rng.Formula = rng.Value
End Sub

SpecialCells can only handle 8192 Areas - I haven't added any code to
account for that as you sample is well below that limit.

--
Regards,
Tom Ogilvy


"cmcgue" wrote:

How do I copy down a set of data in a column when there are varying
spaces
between the rows? I can do this with the following:

For x = 1 To 12
Range(ActiveCell, ActiveCell.End(xlDown).Offset(-1, 0)).Select
Selection.FillDown
ActiveCell.End(xlDown).Select
Next x

This accomplishes the select down copy cells that I need but only if I
know
the number of times to execute. How do you do obtain the same results
when
you don't know the number of times to execute? I want to go to end of
the
column until there is no data remaining and I want to account for
instances
where there are consecutive rows.