macro to fill down values across multiple columns

[sashazaliz]

I'm working with the below macro which works on the first column by filling down the values from the row above in order to create a flat file format. My issue is I need the macro to perform this function on the first 3 columns. Is there a tweak to the code somebody could reccommend for me? thanks!

Sub InsertManufNames()
'the purpose of this macro is the insert the mfr names where they come in blank
     ActiveCell.SpecialCells(xlLastCell).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
 LastRow = ActiveCell.Row + 1
For I = 1 To LastRow
If Cells(I, 1) <> "" Then
    If Cells(I + 1, 1) = "" Then
    Cells(I + 1, 1) = Cells(I, 1)

End If
End If
Next I
End Sub

 

[VoG]

Try this

Sub fillin()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Range("A1:C" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

 

[sashazaliz]

thanks for your reply VoG. It filled the first 3 columns with REF# and not the actual values from the rows above. The original macro I pasted does work but just for the first column. Is there a way we can just tweak that macro to work across the first 3 columns? thanks

 

[VoG]

Odd, that worked for me.

Try

Sub fillin()
Dim LR As Long, c As Range
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Range("A1:C" & LR)
    For Each c In .SpecialCells(xlCellTypeBlanks)
        c.Value = c.Offset(-1).Value
    Next c
End With
End Sub

 

[raeannb]

Sub InsertManufNames()
'the purpose of this macro is the insert the mfr names where they come in blank
     ActiveCell.SpecialCells(xlLastCell).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
 LastRow = ActiveCell.Row + 1
For I = 1 To LastRow
If Cells(I, 1) <> "" Then
    If Cells(I + 1, 1) = "" Then
    Cells(I + 1, 1) = Cells(I, 1)
 End If
End If
Next I
End Sub

Try instead for your loop:

For j = 1 to 3
For I = 1 To LastRow
If Cells(I, j) <> "" Then
    If Cells(I + 1, j) = "" Then
    Cells(I + 1, j) = Cells(I, 1)
End If
End If
Next I
Next j

That should get your first three columns. Good luck!

 

[sashazaliz]

works nicely. thank you so much!