Excel – Fill Values from Above (vb.net)

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s