cmiles - info

Life, Tech and Unimportant Minutiae

Excel - Fill Values from Above (vb.net)

Created by cmiles on 8/18/2006.

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

Try
currentString = valstr(loopCell)
Catch ex As Exception
Continue For
End Try

If currentString = "" Then
loopCell.Value = loopCell.Offset(-1, 0).Value
End If

Next loopCell

Interaction.AppActivate("Microsoft Excel")


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) _
As String

Dim StringResult As String = CType(RangeToConvert.Value, String)

Return StringResult

End Function

Enjoy! CM


Tags:
Posts Before/After: