Autofill UP - Is this the Best Way?

[Guest]

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

 

[Bernie Deitrick]

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