This idea and code is super simple – but I use it multiple times each day and thought that someone might enjoy it. This routine loops thru each cell in the current selection, if the cell has a value it is skipped – if it does not have a value the value from the cell above it is copied into the cell. The same results can be achieved with copy/paste or dragging – but for the kind of data I work with filling from above is often much more efficient. (note – this routine can be a slow, but to keep things light and simple I have not bothered with turning off screen updating or other Excel modes that can increase speed).
Dim completeRange As Excel.Range = rng(xlApp.Selection)
For Each loopCell As Excel.Range In completeRange
Dim currentString As String
currentString = valstr(loopCell)
Catch ex As Exception
If currentString = "" Then
loopCell.Value = loopCell.Offset(-1, 0).Value
I use Interaction.AppActivate(“Microsoft Excel”) to return focus to Excel from my form and save an extra keystroke.
This code uses a helper function (below) to get the string value from the range.
Public Shared Function valstr(ByVal RangeToConvert As Excel.Range) _
Dim StringResult As String = CType(RangeToConvert.Value, String)