Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill UP - Is this the Best Way?
Have the following spreadsheet:
Row Col A Col B Col C Col D 1 John 2 John 3 John 8323 045 59345 4 Ed 5 Ed 6 Ed 7 Ed 8 Ed 7013 014 12345 I need to autofill up; i.e., go to the last entry in Columns B, C, and D (which I can do by offsetting from Col A (which has an entry in each row), then for each blank in Cols B/C/D, fill it with the value from below, all the way up through Row 1. The # of blanks between each entry could be none, 1 or more. Below is the coding I'm using to autofill Col B (and just duplicating to autofill Cols C and D but with different offset numbers), but was wondering if there is a better way to do this? Note: I have to do 'trim' because many of the cells that appear blank actually have spaces; 'trim' takes alot of time--any way to get around that also? Dim rngToSearch As Range Dim rngToTrim As Range With wks Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp)) Set rngToTrim = Range("B1:B50030") End With For Each rng In rngToTrim rng.Value = Trim(rng.Value) Next For each rng In rngToSearch If rng.Offset(0, 1) = "" Then rng.Offset(0, 1).FormulaR1C1 = "=R[1]C" End If Next rng Thanks for any suggestions....Paige |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill UP - Is this the Best Way?
Paige,
Try something like this - if your 'blank' cells with spaces just have one space.... Dim myRange As Range Set myRange = wks.Range("B:B") myRange.Replace What:=" ", Replacement:="", LookAt:=xlWhole myRange.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C" myRange.Copy myRange.PasteSpecial Paste:=xlPasteValues HTH, Bernie MS Excel MVP "Paige" wrote in message ... Have the following spreadsheet: Row Col A Col B Col C Col D 1 John 2 John 3 John 8323 045 59345 4 Ed 5 Ed 6 Ed 7 Ed 8 Ed 7013 014 12345 I need to autofill up; i.e., go to the last entry in Columns B, C, and D (which I can do by offsetting from Col A (which has an entry in each row), then for each blank in Cols B/C/D, fill it with the value from below, all the way up through Row 1. The # of blanks between each entry could be none, 1 or more. Below is the coding I'm using to autofill Col B (and just duplicating to autofill Cols C and D but with different offset numbers), but was wondering if there is a better way to do this? Note: I have to do 'trim' because many of the cells that appear blank actually have spaces; 'trim' takes alot of time--any way to get around that also? Dim rngToSearch As Range Dim rngToTrim As Range With wks Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp)) Set rngToTrim = Range("B1:B50030") End With For Each rng In rngToTrim rng.Value = Trim(rng.Value) Next For each rng In rngToSearch If rng.Offset(0, 1) = "" Then rng.Offset(0, 1).FormulaR1C1 = "=R[1]C" End If Next rng Thanks for any suggestions....Paige |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill | Excel Worksheet Functions | |||
Autofill Until | Excel Programming | |||
AutoFill | Excel Discussion (Misc queries) | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming |