Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Column Fill (2000)

    Is there a piece of code that will fill in blank cells under a filled cell with the value of the filled cell? Then when it gets to a row with the next filled cell change to that value for filling in the blank cells under it. Then repeating the process as often as necessary until the column is completely filled.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Column Fill (2000)

    Start by selecting the empty cellsthis example is for cols A&B change as appropriate)

    Select columns A& B
    Choose Edit - Go To -<Special> - select Blanks <OK>

    Type an equal sign (=)
    Press the "up arrow" on the keyboard
    Hold the Ctrl key and press Enter


    Select columns A& B
    Choose Edit - Copy
    With the columns still selected, choose Edit - Paste Special - values - <OK>

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Column Fill (2000)

    If you need to use VBA code, the following might help,

    Sub AutoFillDown()
    Dim oRng As Range, c As Range
    Set oRng = Range("A:A").SpecialCells(xlCellTypeConstants, 23)
    For Each c In oRng
    Range(c.Offset(1, 0), c.End(xlDown).Offset(-1, 0)) = c.Value
    Next
    End Sub


    This example uses Column A, and will fill the entire 65,536 cells. If you are only working with say the first 5000 rows, you could change "A:A" to "A1:A5000", and insert some dummy data in A5001 to act as an end marker.

    Andrew C

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Fill (2000)

    Steve:

    Just I would add the following one, to make it easier:

    Make Excel formulas permanent with your mouse (95/97/2000)

    One of the tasks that users commonly perform in Excel is converting
    formula results to fixed values. To do this, you probably copy the
    formula and then use the Paste Special dialog box to paste the
    permanent values over the original formulas. However, there's a much
    easier way that doesn't require any trips to the menu bar or Paste
    Special dialog box. First, select the range of formulas you want to
    convert. Then, using the right mouse button, drag the selected range
    one cell in any direction, and then return the range to its original
    location. When you release the mouse button, Excel displays a
    shortcut menu. Simply select Copy Here As Values Only (Copy Values
    in 95) to make the formula results permanent.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Column Fill (2000)

    Servando,
    While this is a great tip, it will NOT work in the case I proposed, since the selction is NOT a contiguous range, so you MAY NOT drag it anywhere.

    The only way you could use the technique your propose with what I propose is to change the selection from JUST the formulas that occupy the formerly blank cells and reselect both columns then copy and paste.

    I am not sure that it would be any easier.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Fill (2000)

    Servando that is such a common operation it's worth writing a macro and putting it on a toolbar button:

    Sub ReplaceWithValues()

    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = True
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Fill (2000)

    Steve:

    ===========================================
    Select columns A& B
    Choose Edit - Copy
    With the columns still selected, choose Edit - Paste Special - values - <OK>
    ===========================================

    My proposal applies and works, same way that you propose, but I consider that with less steps. Anyway is a tip and it is the same result. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts

    Re: Column Fill (2000)

    Michael,

    That is a great idea.
    But you don't need no macro.
    Just customize any toolbar with the built-in paste-values button!
    Use Customize, select Edit category and scroll to Paste values button, then drag it tou your toolbar.
    While your 'e there, why not drag the Paste-Formating button too.

    zeddy

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Column Fill (2000)

    You are correct. I forgot in the second part of what I did that I had to reselect the columns.

    Your way is more effiecient than the way I listed. When I do it I select the columns and press the copy button and then the paste-values (built-in) button I added next to it and don't go thru edit at all.

    I suppose I could add a separate button that did both as was also suggested., but I sometimes copy and don't paste-values and I sometimes paste -values from a different location, so having both seems more effective to me.

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Fill (2000)

    Zeddy

    If you just use the Paste Special - Values Only button you have first to copy the section you are converting to values, then PS-VO.

    The macro I suggested incorporates the copying, so all you have to do is highlight the selection and press the custom button.

    Yep I have the inbuilt Paste Special buttons (Values and Formats) on my toolbar, also the following:

    Sub PasteSpecialFormulas()
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End Sub

    because MS do not provide an Paste Special Formulas button (not in XL2000 at least).

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Fill (2000)

    Bill

    I think the following macro does what you want:

    Sub FillBlankCellsOneRowOrCol()

    Dim cl
    CountA = 0
    For Each cl In Selection
    If cl = "" Then
    CountA = CountA + 1
    cl.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Else
    cl.Copy
    End If
    Next cl
    Application.CutCopyMode = False
    End Sub

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts

    Re: Column Fill (2000)

    Michael

    You are correct. Seems I hardly notice when I do the Ctrl-C first.
    I like the paste-formulas option.

    zeddy

Posting Permissions

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