Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Copying Data to Blank cells abovbe data in same Column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2015
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copying Data to Blank cells abovbe data in same Column

    I am trying to copy and paste data in column A to the blank cells above it.

    For example: Cell A5 contains "1203" and I want it copied to the blank cells above A1:4
    Cell A12 contains "1237" and I want it copied to the blank cells above A6:11
    Cell A18 contains "1291" and I want it copied to the blank cells above A13:17

    These cell reference are variable and will change depending on the data in the report. I know I need to do this with a loop (I think) but I am confusing myself trying to figure it out.

    Any suggestions?

  2. #2
    Board Regular
    Join Date
    Jan 2014
    Location
    Dublin, Ireland
    Posts
    1,421
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying Data to Blank cells abovbe data in same Column

    Hi, does the below work for you?

    Code:
    Sub test()
        Dim lr As Long, i As Long
        lr = Range("A" & Rows.Count).End(xlUp).Row
        For i = lr To 1 Step -1
        If Range("A" & i).Value = "" Then Range("A" & i).Value = Range("A" & i + 1).Value
        Next
    End Sub
    Learn something new everyday.

    be sure to use code tags

    Code:
    [ code ]
    [ / code ]
    ' no spaces

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Copying Data to Blank cells abovbe data in same Column

    Another option
    Code:
    Sub CopyUp()
       With Range("A:A")
          .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
          .Value = .Value
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Sep 2015
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying Data to Blank cells abovbe data in same Column

    It didn't work but I think it may have to do this my coding before it. In column C there are random data in cells that look like "Totals for Cost Center 0120140100 - DIRECTOR". I am trying to loop through column C to find these cells and only take the "120140100" portion of the string and copy it over to the adjacent cell in column A. One the loop was completed I want to copy and paste as above. Here is my coding:


    'Set variables
    Dim LastRowSH As Long
    LastRowSH = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row

    Dim Data As Worksheet
    Set Data = ThisWorkbook.Sheets("Data")

    Dim x As Long

    'Find cost centres in column C and use MID function to copy the cost centre to adjacent cell in Column A
    For x = 2 To LastRowSH - 1

    If Data.Cells(x, 3).Value Like "*Total*" Then
    Data.Cells(x, 3) = Mid(Cells(x, 3), 25, 9).Offset(-2, 0)
    End If
    Next x

    'Delete column C
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft

    'Copy cost centres to above empty cells
    With Range("A:A")
    .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
    .Value = .Value
    End With

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,111
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Copying Data to Blank cells abovbe data in same Column

    Maybe change
    Code:
    If Data.Cells(x, 3).Value Like "*Total*" Then
    Data.Cells(x, 3) = Mid(Cells(x, 3), 25, 9).Offset(-2, 0)
    End If
    Next x
    to
    Code:
    If Data.Cells(x, 3).Value Like "*Total*" Then
    Data.Cells(x, 1) = Mid(Cells(x, 3), 25, 9)
    End If
    Next x
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    Board Regular
    Join Date
    Sep 2015
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying Data to Blank cells abovbe data in same Column

    That did not work either I was trying to avoid using text to columns but I may have to.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Copying Data to Blank cells abovbe data in same Column

    In what way didn't it work?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,111
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Copying Data to Blank cells abovbe data in same Column

    You need to give more description than just "did not work" in your posts.

    Also change
    Code:
    If Data.Cells(x, 3).Value Like "*Total*" Then
    Data.Cells(x, 1) = Mid(Cells(x, 3), 25, 9)
    End If
    Next x
    to
    Code:
    If Data.Cells(x, 3).Value Like "*Total*" Then
    Data.Cells(x, 1) = Mid(Data.Cells(x, 3), 25, 9)
    End If
    Next x
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    Board Regular
    Join Date
    Sep 2015
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying Data to Blank cells abovbe data in same Column

    I have the MID function working now. Thanks

    I think I need a loop to find the value in column a (A5 for example) and paste it to the above blank cells (A1:4) and then go find the next value say A15 and paste it to the above blank cells A6:14, and so on.

    The coding provided above just give me "0" in Column A
    Last edited by SAMCRO2014; Nov 6th, 2018 at 01:41 PM.

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,111
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Copying Data to Blank cells abovbe data in same Column

    Code:
    With Data.Range("A:A")
      .SpecialCells(xlBlanks).FormulaR1C1 = "=R[1]C"
      .Value = .Value
    End With
    works fine for me
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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