Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting column (Excel XP (2002))

    A simple request, (I think), but I can't seem to find the answer elsewhere.
    After inserting a new blank column, how do I copy a formula down to the last row, but not beyond?

    Thanks,

    StephenS

  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: Selecting column (Excel XP (2002))

    Put the formula in the top cell.
    Move the cursor to the bottom right corner of the cell (cursur changes to "+")
    Dbl-click [It will autofill as far down as is filled in the neighboring columns)

    Steve

  3. #3
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting column (Excel XP (2002))

    Brilliant. Thank you.

    I knew it had to be easy, but I didn't realise how easy.

    StephenS

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Selecting column (Excel XP (2002))

    Hi Steve
    Interesting coincidence. One of my users just came to me and asked why she could not do this. Double-clicking on her fill handle doesn't do anything. Any ideas?
    Thanks for your help.

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

    Re: Selecting column (Excel XP (2002))

    Double-clicking the fill handle of a cell only works if the column immediately to the left or immediately to the right of the cell is filled. If there are empty columns on both sides of the cell, double-clicking the fill handle will have no effect.

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Selecting column (Excel XP (2002))

    Hi Hans
    I tried it on a very simple sheet. A1:A3 holds 1,2,3; B1=a1+1; double-clicking fill handle doesn't work.

    It works fine on most (all?) our other PCs, so it seems that there must be a setting which has been disabled on this one PC. She doesn't have any unusual add-ins that other users don't have.

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

    Re: Selecting column (Excel XP (2002))

    Sorry, no idea. You could try Jan Karel Pieterse's <!post=Systematic Approach to Behavioral Problems in XL,290455>Systematic Approach to Behavioral Problems in XL<!/post> on her system.

  8. #8
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting column (Excel XP (2002))

    Is there a way of incorporating this function into a macro, so that the formula will fill down on sheets that have varying numbers of rows?

    StephenS

  9. #9
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting column (Excel XP (2002))

    Thanks again, Hans.

    It seems to work fine, although I did have to remove the Exit Function command, which it didn't seem to like for some reason. This will be very useful and save me loads of time.

    Cheers,

    StephenS

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

    Re: Selecting column (Excel XP (2002))

    Edited by HansV to change Exit Function to Exit Sub

    Here is an approximation. It will probably not exactly emulate double clicking the fill handle in all situations, so make sure to test it in your own workbook.

    Sub DblClickFill()
    Dim lngOffset As Long
    If ActiveCell.Column = 1 Then
    lngOffset = 1
    If TestOffset(lngOffset) Then
    Exit Sub
    End If
    ElseIf ActiveCell.Column = Columns.Count Then
    lngOffset = -1
    If TestOffset(lngOffset) Then
    Exit Sub
    End If
    Else
    lngOffset = -1
    If TestOffset(lngOffset) Then
    lngOffset = 1
    If TestOffset(lngOffset) Then
    Exit Sub
    End If
    End If
    End If
    ActiveCell.AutoFill Destination:=Range(ActiveCell, _
    ActiveCell.Offset(0, lngOffset).End(xlDown).Offset(0, -lngOffset))
    End Sub

    Function TestOffset(lngOffset) As Boolean
    TestOffset = (ActiveCell.Offset(0, lngOffset).HasFormula = False And _
    ActiveCell.Offset(0, lngOffset) = "")
    End Function

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

    Re: Selecting column (Excel XP (2002))

    Sorry about that. You shouldn't remove Exit Function, but change it to Exit Sub. I have corrected my previous reply.

  12. #12
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting column (Excel XP (2002))

    I have changed it to Exit Sub and it works very well.

    Best wishes,

    StephenS

Posting Permissions

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