Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to Copy Cell Above (Excel XP-SP3)

    I am trying to have a macro go through a selection and if the cell is blank, copy the contents of the cell above. I tried the following macro but as soon as it finds a blank cell it copied down to the remainder of the selection. It should copy down to the next cell with data and then start checking again.

    <font color=008080> Sub text_Copy_Above()
    Dim oCell As Range

    For Each oCell In Selection
    If (oCell = "") Then
    Selection.FillDown
    End If
    Next oCell
    End Sub </font color=008080>

    TIA
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  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: Macro to Copy Cell Above (Excel XP-SP3)

    Does it have to be a macro?
    It is relatively easy to do it manually, if you do not do it a lot, as can be seen in Excel -- Data Entry -- Fill Blanks

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Copy Cell Above (Excel XP-SP3)

    All I can say is <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    How simple! How fast! How many hours have I spent filling in those darn blank cells? <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

    Thanks Steve.

    <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Copy Cell Above (Excel XP-SP3)

    If you just want to copy the value of the cell above, then you could use:

    <pre>Sub text_Copy_Above()
    Dim oCell As Range
    For Each oCell In Selection
    If (oCell.Value = "") Then
    oCell.Value = oCell.Offset(-1, 0).Value
    End If
    Next oCell
    End Sub
    </pre>


    If you want to copy the cell (value or formla) with cell formatting, then you could use:

    <pre>Sub text_Copy_Above_With_Formatting()
    Dim oCell As Range
    For Each oCell In Selection
    If (oCell.Value = "") Then
    oCell.Offset(-1, 0).Copy Destination:=oCell
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Copy Cell Above (Excel XP-SP3)

    Those both work great!

    Thanks Legare.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

Posting Permissions

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