Solved Macro to copy cells then paste only values in a column

Microsoft Excel 2010 - complete product...
April 2, 2018 at 05:49:19
Specs: Windows 7, 2,4 GHz / 4 GB
Hello,

I would like to be able to copy a variable range of cells (rows and columns) with many blanks into one column with only the data (skip the blanks).

Basically I have a table ranging (in this example) from A1 to F11, with data only in some cells: A11, D1, D5, D6, D11, E1 to E11 (all cells), F1, F5.

Here is an image of what my tables look like:
Image to table

I found a vba code online but it works for copying 1 column of data and blanks and paste only data into a column. I edited it to look into an array (type:=64), but I get 424 object required error.

Dim rng As Range
Dim InputRng As Range, OutRng As Range

xTitleId = "Select"
 
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Range: ", xTitleId, InputRng.Address, Type:=64)

    Set OutRng = Application.InputBox("Output to: ", xTitleId, Type:=8)
    
InputRng.SpecialCells(xlCellTypeConstants).Copy Destination:=OutRng.Range("A1")

I was looking for a simple 1 liner to just paste values without blanks to the same column, but found no such request elsewhere (googled) so no code.

I would prefer a 1 line paste-just-data-vba-code. If not, could you please help me to adapt the above code so that when I paste in (eg) J1, I get only the data without the blanks? Like A11 to J1, D1 to J2, D5 to J3 and so on.

Thank you in advance.

message edited by Mrrrr


See More: Macro to copy cells then paste only values in a column

Report •

✔ Best Answer
April 4, 2018 at 19:59:58
Sub RowCol()
Dim InputRng As Range
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Range: ", xTitleId, InputRng.Address, Type:=8)
  
'Declare array based on size of Range
 Dim myArray() As String
 ReDim myArray(InputRng.Cells.Count) As String
 
'Loop through InputRng by individual Column
  With InputRng
    For col = 1 To .Columns.Count
      For rw = 1 To .Rows.Count
        If .Cells(rw, col) <> "" Then
          e = e + 1
          myArray(e) = .Cells(rw, col)
       End If
      Next
    Next
  End With
  
'Spit out Array elements into Column J
   For outRow = 1 To UBound(myArray)
     Cells(outRow, "J") = myArray(outRow)
   Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
April 2, 2018 at 12:30:24
This isn't pretty, but I don't have to work on it much since I am traveling.

The concept is to loop through the Range building an array from the cells that contain values. Once the array is built, loop through the array outputting the elements into Column J.

Sub outputArray()
'Declare array based on size of Range
 Dim myArray() As String
 ReDim myArray(Range("A1:F11").Cells.Count) As String
   
'Loop through Range, building Array with values
   For Each cell In Range("A1:F11")
      If cell <> "" Then
        e = e + 1
        myArray(e) = cell
      End If
    Next
 
'Spit out Array elements into Column J
   For outRow = 1 To UBound(myArray)
     Cells(outRow, "J") = myArray(outRow)
   Next
 
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
April 3, 2018 at 00:15:59
Thanks for the macro. It works great with a fixed range.

I modified it to accept a variable range by merging into it a part of my first code.

    Dim InputRng As Range
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Range: ", xTitleId, InputRng.Address, Type:=8)

'Declare array based on size of Range
 Dim myArray() As String
 ReDim myArray(InputRng.Cells.Count) As String
   
'Loop through Range, building Array with values
   For Each cell In InputRng
      If cell <> "" Then
        e = e + 1
        myArray(e) = cell
      End If
    Next
 
'Spit out Array elements into Column J
   For outRow = 1 To UBound(myArray)
     Cells(outRow, "J") = myArray(outRow)
   Next

The only problem I have is that it copies value by row and not by column, meaning:
- NOW it copies them in this order: D1-E1-F1-E2-E3 etc.
- I WOULD NEED: A11-D1-D5-D6-D11-E1-E2 etc.

Transposing InputRng doesn't help because then row11 gets in the last column, while I need cell A11 to be first in the pasted values.

What would you suggest?

message edited by Mrrrr


Report •

#3
April 3, 2018 at 05:40:01
First, some background:

A For-Each loop defaults to looping from left to right, then down. i.e. across rows first. That is because cells in a range are numbered from left to right. You can refer to individual cells in a range by using it's number within the range.

e.g. In the range of B5:C7

Cell(1) is B5
Cell(2) is C5
Cell(3) is B6
Cell(4) is C6
etc.

In addition, when referring to a specific range, the Cells(rowindex, columnindex) method can be used to refer to only the cells in the range.

e.g. Cells(1, 1) in B5:C7 is B5.

Now let's use those concepts:

If you want to loop by Row then by Column, you could use a nested loop. The following code is just an example for illustration. You would need to replace the Select instruction with the array building code

Sub RowCol()
Dim InputRng As Range
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Range: ", xTitleId, InputRng.Address, Type:=8)
  
'Loop through InputRng by individual Column
  With InputRng
    For col = 1 To .Columns.Count
      For rw = 1 To .Rows.Count
       .Cells(rw, col).Select
      Next
    Next
  End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
April 3, 2018 at 23:53:29
re: You would need to replace the Select instruction with the array building code.

And will it work for my dynamic selection? In my example I had A1:F11, yes, but I want to be able to select different random ranges (B7:G13, C3:E10 etc.) and apply my macro for those. I don't know how to replace the selection instruction with the array building code and still have that.

I added the array building code inside that loop by column and stepped with F8 through it, but I can't get nothing out of the array to column J. I clearly must be doing it wrong. I tried to Debug.Print e and in Immediate window it displays 0 1 2 3 etc. - shouldn't it display the value of myarray(0), myarray(1) etc?

With the help of your loop by individual column, I removed the array entirely and made a simple copy value if <> "" to column J. The code is below. I don't know how to do it with array.

Dim InputRng As Range
Set InputRng = Application.Selection

'Loop through InputRng by individual Column
  With InputRng
    For col = 1 To .Columns.Count
      For rw = 1 To .Rows.Count
       .Cells(rw, col).Select
                If ActiveCell.Value <> vbNullString Then
                       ActiveCell.Copy ActiveSheet.[J1].Offset(e, 0)
                      e = e + 1
                End If
      Next
    Next
  End With

I won't have more than 40 values in each range that I need to copy. Do you think an array would be faster? If so, could you please help with You would need to replace the Select instruction with the array building code for my original code?

message edited by Mrrrr


Report •

#5
April 4, 2018 at 19:59:58
✔ Best Answer
Sub RowCol()
Dim InputRng As Range
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Range: ", xTitleId, InputRng.Address, Type:=8)
  
'Declare array based on size of Range
 Dim myArray() As String
 ReDim myArray(InputRng.Cells.Count) As String
 
'Loop through InputRng by individual Column
  With InputRng
    For col = 1 To .Columns.Count
      For rw = 1 To .Rows.Count
        If .Cells(rw, col) <> "" Then
          e = e + 1
          myArray(e) = .Cells(rw, col)
       End If
      Next
    Next
  End With
  
'Spit out Array elements into Column J
   For outRow = 1 To UBound(myArray)
     Cells(outRow, "J") = myArray(outRow)
   Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#6
April 5, 2018 at 01:41:57
That works great, thank you very much.
I removed the InputBox because it was pointless after all, my bad.

Report •

#7
April 7, 2018 at 12:10:57
re: "I tried to Debug.Print e and in Immediate window it displays 0 1 2 3 etc. - shouldn't it display the value of myarray(0), myarray(1) etc?"

I didn't have time to address this question the other day.

Debug.Print e is only going to display the value of the variable "e", which is nothing more than a counter. You would have needed to have used to Debug.Print myArray(e) if you wanted to see myArray(1), myArray(2), etc.

In other words "e" has no relationship to the actual array. It's just a variable. You will note that the array was built using "e" as the counting variable but was outputted using the variable "outRow". The name used for the counting variable doesn't matter.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Ask Question