Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting/Totalling Macro with varying rows of data | Excel Discussion (Misc queries) | |||
Using macro/vba to copy varying ranges of rows | Excel Discussion (Misc queries) | |||
SUMPRODUCT with varying # of rows | Excel Worksheet Functions | |||
Sum varying number of rows | Excel Programming | |||
Using a Macro to perform a VLookup on varying number of rows | Excel Programming |