Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find range and fill down
range starts at c25.
need to find last cell used in column c. highlight/select all cells in between. = range fill down any empty spaces with value from cell above (within range) (list/data will be incomplete. need to fill in empty spaces only within used range down, but not to extend past last used cell). thanx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find range and fill down
Very Easy Sub fillin() LastRow = Range("C" & Rows.Count).End(xlUp).Row OldData = Range("C25") For RowCount = 26 To LastRow If Range("C" & RowCount) = "" Then Range("C" & RowCount) = OldData Else OldData = Range("C" & RowCount) End If Next RowCount End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=179831 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find range and fill down
This macro should execute quicker...
Sub FillInTheBlanks() Dim Area As Range, LastRow As Long On Error Resume Next LastRow = Cells(Rows.Count, "C").End(xlUp).Row For Each Area In Range("C25").Resize(LastRow). _ SpecialCells(xlCellTypeBlanks).Areas Area.Value = Area(1).Offset(-1).Value Next End Sub -- Rick (MVP - Excel) "joel" wrote in message ... Very Easy Sub fillin() LastRow = Range("C" & Rows.Count).End(xlUp).Row OldData = Range("C25") For RowCount = 26 To LastRow If Range("C" & RowCount) = "" Then Range("C" & RowCount) = OldData Else OldData = Range("C" & RowCount) End If Next RowCount End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=179831 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find range and fill down
Rick : Your code may not work in this situation. I think the person has an ID in column a but no in every row so your code copies the same ID down the entire worksheet. For example Row Column A 1 Apple 2 3 4 5 6 Bananna 7 8 9 10 11 Pear 12 13 14 15 Grape -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=179831 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find range and fill down
Another one.
Sub fillinblank() Dim startrow As Long, i As Long Dim Lastcell As Range, Blankrng As Range startrow = 25 Set Lastcell = Cells(Rows.Count, "C").End(xlUp) Set Blankrng = Range(Cells(startrow, "C"), Lastcell) _ .SpecialCells(xlCellTypeBlanks) For i = 1 To Blankrng.Areas.Count Blankrng.Areas(i) = Blankrng.Areas(i).Resize(1, 1).Offset(-1).Value Next End Sub Keiji J.W. Aldridge wrote: range starts at c25. need to find last cell used in column c. highlight/select all cells in between. = range fill down any empty spaces with value from cell above (within range) (list/data will be incomplete. need to fill in empty spaces only within used range down, but not to extend past last used cell). thanx |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
find range and fill down
For the most part, my macro does exactly what your macro does; however, as
written, it copies down to the end of the UsedRange for the worksheet. The following minor modification makes my macro duplicate the results of your macro... Sub FillInTheBlanks() Dim Area As Range, LastRow As Long On Error Resume Next LastRow = Cells(Rows.Count, "C").End(xlUp).Row For Each Area In Intersect(Rows("25:" & LastRow), Range("C25"). _ Resize(LastRow).SpecialCells(xlCellTypeBlanks)).Ar eas Area.Value = Area(1).Offset(-1).Value Next End Sub Because it is handling empty Areas at a time, this should still be faster than iterating each individual cell (unless there is no groupings of empty cells, that is, unless each empty cell stands alone). -- Rick (MVP - Excel) "joel" wrote in message ... Rick : Your code may not work in this situation. I think the person has an ID in column a but no in every row so your code copies the same ID down the entire worksheet. For example Row Column A 1 Apple 2 3 4 5 6 Bananna 7 8 9 10 11 Pear 12 13 14 15 Grape -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=179831 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where to find pattern fill add-in | Charts and Charting in Excel | |||
Where to find pattern fill add-in | Excel Discussion (Misc queries) | |||
SumIf - when I fill down the Range, Criteria & sum range changes | Excel Worksheet Functions | |||
Find non blank cell in range and fill the rest with that value | Excel Programming | |||
Clear if "#N/A" and Find End of Range, Fill Blanks | Excel Programming |