Fill with Above (Excel 97 SR2)

Topic

New Reply

Hi, I have a macro that I got off the internet, likely from this lounge, a few years ago. I made the mistake of giving it to a co-worker and now that co-worker wants the macro modified

What she’s doing is taking data from a pivot table, copying that data to a new worksheet, using the fill with above macro to copy data down in the blank cells, and then doing a vlookup on that copied column.

The trouble is the vlookup only works on the first (original) row. The other ones return N/A. If you copy the top cell down the vlookup works, but pasting formats doesn’t work. Both cells say they’re formatted as general. She doesn’t want to have to do any copying or anything manually, she wants it all done automatically through the macro or formulas. She has tried to multiply the column by 1, but she said that caused some other kind of problem, though I didn’t see it to know what problem she’s talking about.

Can anyone tweak the macro to copy all of the information down instead of just the values?

Thanks,

Brett

Sub FillWithAbove()
‘
‘ Fill With Above Macro
‘ Macro recorded 10/29/99

Dim WithWhat As Variant
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
For iC = 1 To iColumns
WithWhat = Selection.Item(1, iC).Value
For iR = 1 To iRows
If Selection.Item(iR, iC).Value = “” Then
Selection.Item(iR, iC).Value = WithWhat
Else
WithWhat = Selection.Item(iR, iC).Value
End If
Next iR
Next iC
End Sub

Reply | Quote

Replies

This macro will copy and paste the cell above to an empty cell. You or your co-worker should test carefully whether it works as intended.

Sub FillWithAbove()
Dim iRows As Long, iR As Long
Dim iColumns As Long, iC As Long
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
For iC = 1 To iColumns
For iR = 2 To iRows
If Selection.Item(iR, iC).Value = “” Then
‘ Copy cell above and paste into empty cell
Selection.Item(iR – 1, iC).Copy Selection.Item(iR, iC)
End If
Next iR
Next iC
End Sub

Note that this version starts at row 2 of the selection, to prevent errors with empty cells in row 1 of a worksheet.

Reply | Quote

Hans,

Thanks! That seems to work. As you advised, we’ll keep an eye on the results to make sure it’s working entirely as intended, but our initial test showed success!

Thanks again!

Brett

Reply | Quote

Another fast way

Sub FillWithAbove()
‘
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = “=R[-1]C”
Range(“A1”).Select
End Sub

Reply | Quote