Thread: Copy cells from column G to column A (if <> blank)

So hard to fit everything into the title. Basically I need a macro that will take some value data from a cell range of lets say G5:G29 and then paste this value only data into the same row range of column A IF the beginning cell is blank. However, if that cell is NOT blank then I need the macr to move to the next column ToRight and see if that cell is blank and continue this action until if finds the cell on row 5 column(XX) that is blank, and then value paste that data there. Does that makes sense?

I have attached an example. Thanks for the help in advance.

Hello Jim,

I added the following macro to the attached workbook. It starts in row 5 with column "G". It looks at column "A" to check if it is blank and if it is then copies cell "G" into it. If "A" is full then the next column is checked and so on. The macro will not allow you to beyond column "E". A button has been added to run the macro.

Sub CopyData()

  Dim Cell As Range
  Dim DstCell As Range
  Dim RngEnd As Range
  Dim SrcRng As Range
  
    Set SrcRng = Range("G5")
    Set RngEnd = Cells(Rows.Count, SrcRng.Column).End(xlUp)
    Set SrcRng = IIf(RngEnd.Row < SrcRng.Row, SrcRng, Range(SrcRng, RngEnd))
    
      For Each Cell In SrcRng
        Set DstCell = Cells(Cell.Row, "A")
        If IsEmpty(DstCell) Then
           DstCell = Cell
        Else
           On Error Resume Next
             Set DstCell = DstCell.Resize(1, 5).SpecialCells(xlCellTypeBlanks).Cells(1, 1)
             If Err = 0 Then DstCell = Cell
           Err.Clear
        End If
      Next Cell
      
End Sub

Originally Posted by Leith Ross

Hello Jim,

I added the following macro to the attached workbook. It starts in row 5 with column "G". It looks at column "A" to check if it is blank and if it is then copies cell "G" into it. If "A" is full then the next column is checked and so on. The macro will not allow you to beyond column "E". A button has been added to run the macro.

Sub CopyData()

  Dim Cell As Range
  Dim DstCell As Range
  Dim RngEnd As Range
  Dim SrcRng As Range
  
    Set SrcRng = Range("G5")
    Set RngEnd = Cells(Rows.Count, SrcRng.Column).End(xlUp)
    Set SrcRng = IIf(RngEnd.Row < SrcRng.Row, SrcRng, Range(SrcRng, RngEnd))
    
      For Each Cell In SrcRng
        Set DstCell = Cells(Cell.Row, "A")
        If IsEmpty(DstCell) Then
           DstCell = Cell
        Else
           On Error Resume Next
             Set DstCell = DstCell.Resize(1, 5).SpecialCells(xlCellTypeBlanks).Cells(1, 1)
             If Err = 0 Then DstCell = Cell
           Err.Clear
        End If
      Next Cell
      
End Sub

Leith,
Thanks so much for your expertise on this one. I was struggling trying to understand the documentation on the if else commands of VB with Excel. I do have a question though, without actually testing it yet to bare with me, about if this is restricted to just G5 or can I set the SrcRng var to G5:G29?

Thanks!

Hello Jim,

You can restrict to "G5:G29". I wrote the code to start at "G5" and find the last cell in column "G" with an a value. That way the range can be dynamic. The range can change size but you don't have to change the code. The code below is what starts the range at "G5" and finds the last value in the column.

    Set SrcRng = Range("G5")
    Set RngEnd = Cells(Rows.Count, SrcRng.Column).End(xlUp)
    Set SrcRng = IIf(RngEnd.Row < SrcRng.Row, SrcRng, Range(SrcRng, RngEnd))

So here is a possible hiccup. In the spreadsheet I will be using, not every cell in column G will have a value. There will be some cells in the originating column of G where values will not be present but I still need the loop to continue until row 29. I apologize for not being clear.
I am attempting to replace the manual effort of taking all cells in G5:G29 and pasting values in the first blank column. This will update weekly and each week I will populate new values into the next column (B) to capture historical data for the month. I am sure you figured that out, but I wanted to make sure I was clear on what I was looking for . Thanks again for your help!

Hello Jim,

I want to make sure I understand what you want before I change the macro. As it is now the macro copies and pastes column "G5:G29" into column "A,B,C,D or E" with any blanks that appear column "G". Do you not want blanks in column "A:E" like you have in column "G"?

Yes, So just to walk through the visual of what would happen in theory.

The macro begins by checking to make sure column A is blank, first cell A5 is fine as that will "always" have something in there if the column was populated previously. If there is something in A5 then it moves to the next column in validation to see if it is blank, and so forth until it reaches a blank cell in row 5 of columns A through E.

Next the macro takes all values, even some cells that are blank, from G5:G29 and pastes values into the column from the validation process above.

Does that makes sense? I can hit up a MS Live Meeting or NetMeeting with you if you want me to perform the task visually. Not sure if that would help but I would be happy to host it.

Hello Jim,

I changed the macro to examine columns "A:E" and to fill the first empty column found. Here is the revised macro. This has been added to the attached workbook.

Sub CopyData()

  Dim Cell As Range
  Dim DstCell As Range
  Dim RngEnd As Range
  Dim SrcRng As Range
  
    Set DstRng = Range("A5:E5")
    Set SrcRng = Range("G5")
    Set RngEnd = Cells(Rows.Count, SrcRng.Column).End(xlUp)
    Set SrcRng = IIf(RngEnd.Row < SrcRng.Row, SrcRng, Range(SrcRng, RngEnd))
    
      For Each Cell In DstRng
        If WorksheetFunction.CountA(Cell.Resize(SrcRng.Rows.Count, 1)) = 0 Then
           Cell.Resize(SrcRng.Rows.Count, 1).Value = SrcRng.Value
           Exit For
        End If
      Next Cell
      
End Sub