Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling gaps (2002)

    I have a spreadsheet that comes from a dump of a database which I have no control over.
    Not all the rows get a value put into them, only the rows where the value changes. (see eattached for clarity)
    How can I write a proceedure that will autoomatically fill in the gaps. In real life the sheet is huge and therefore I dont want to do it by hand.
    See example sheet attached.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling gaps (2002)

    The following macro will do this for the first column in the selection. If you want to extend it to more columns, that shouldn't be difficult

    Sub FillGaps()
    Dim rngSel As Range
    Dim lngRowIndex As Long
    Set rngSel = Selection
    For lngRowIndex = 2 To rngSel.Rows.Count
    If rngSel.Cells(lngRowIndex, 1) = "" Then
    rngSel.Cells(lngRowIndex, 1) = rngSel.Cells(lngRowIndex - 1, 1)
    End If
    Next lngRowIndex
    Set rngSel = Nothing
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling gaps (2002)

    In your example, in cell E5 put:

    =IF(ISNUMBER(B5),B5,E4)

    Copy down as far as you need.

    Regards
    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •