cmiles - info

Life, Tech and Unimportant Minutiae

Excel - Fill Values from Above (

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

        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

Posts Before/After: