Need code to copy ONLY non-empty cells in a column to a new range.

[d0rian]

See image below.

• I want VBA that will copy any non-empty cells in a column (BZ7, BZ11, BZ14 in this case) to a new range with NO empty cells separating them (e.g. CA1, CA2, CA3 below). I did it manually in the image below to show my desired outcome, but I basically want VBA that will let me accomplish that with a single click.
• **Important**: "Non-empty" is actually not quite true: every cell in Column BZ has a formula in it that returns either the characters you see OR null value (""). So the BZ cells that appear blank aren't actually empty, they simply have a null value, so I actually want to copy any cells for which (Length > 0), since none are truly empty.
• Lastly, in an ideal world, I want to paste whatever cells I copy to the first non-empty cell in column CA. So the first paste (shown below) will start in CA1. But the next paste will start in CA4, etc. I'm basically adding onto a growing list in col CA with each successive paste. (I know how to create a helper cell with the value of the first empty cell, e.g. CA4, so can put that in, say, cell CB1 as a reference if that makes this easier.)

 

[offthelip]

this will do it:

Sub test()
Dim outarr As Variant


lastrow = Cells(Rows.Count, "BZ").End(xlUp).Row
inarr = Range(Cells(1, 78), Cells(lastrow, 78))


ReDim outarr(1 To lastrow, 1 To 1)
indi = 1
For i = 1 To lastrow
  If Len(inarr(i, 1)) > 0 Then
   outarr(indi, 1) = inarr(i, 1)
   indi = indi + 1
  End If
Next i
 
Range(Cells(1, 79), Cells(indi, 79)) = outarr
 


End Sub

 

[d0rian]

Thank you, this works for everything except my 3rd bullet point -- i.e. this code seems to always simply start the paste in cell CA1 (and will paste over any values already there)...whereas I'd like the paste to start in the first blank cell in col CA (e.g. CA4 in the image above...)

 

[Rick Rothstein]

Assuming your posted sample data is representative of your actual data (the cell values contain no space characters), then this should work for you...

[table="width: 500"]
[tr]
	[td]Sub CopyNonEmptyCellsFromColumnBZtoColumnCA()
  Dim LastRow As Long, Arr As Variant
  LastRow = Cells(Rows.Count, "BZ").End(xlUp).Row
  Arr = Split(Application.Trim(Join(Application.Transpose(Range("BZ1:BZ" & LastRow)))))
  Cells(Rows.Count, "CA").End(xlUp).Offset(1).Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub[/td]
[/tr]
[/table]

 

[d0rian]

Great, thanks Rick this works! (although I noticed that the very first paste (if col CA is empty) starts in CA2, rather than CA1...but it works properly after that (i.e. it always starts the paste in the first empty cell). Is there anything minor I can tweak to have that initial paste in CA1? If not, nbd since only rarely will CA be truly empty...

 

[d0rian]

Oh, and 1 final query I just thought of. How can I tweak it if I want the paste range to be on a different worksheet? For instance, let's say that instead of pasting to column CA (to the immediate right of the source column BZ) on "Sheet1", I want the paste to be in column A on "Sheet2"?

 

[Rick Rothstein]

Oh, and 1 final query I just thought of. How can I tweak it if I want the paste range to be on a different worksheet? For instance, let's say that instead of pasting to column CA (to the immediate right of the source column BZ) on "Sheet1", I want the paste to be in column A on "Sheet2"?

See if this does what you want...

[table="width: 500"]
[tr]
	[td]Sub CopyNonEmptyCellsFromColumnBZtoColumnCA()
  Dim LastRow As Long, Arr As Variant
  LastRow = Cells(Rows.Count, "BZ").End(xlUp).Row
  Arr = Split(Application.Trim(Join(Application.Transpose(Range("BZ1:BZ" & LastRow)))))
  Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(-(Len(Sheets("Sheet2").Range("A1").Value) > 0)).Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub[/td]
[/tr]
[/table]

 

[JoeMo]

See if this does what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CopyNonEmptyCellsFromColumnBZtoColumnCA()
  Dim LastRow As Long, Arr As Variant
  LastRow = Cells(Rows.Count, "BZ").End(xlUp).Row
  Arr = Split(Application.Trim(Join(Application.Transpose(Range("BZ1:BZ" & LastRow)))))
  Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(-[COLOR=#ff0000][B]CLng([/B][/COLOR]Len(Sheets("Sheet2").Range("A1").Value)[B][COLOR=#ff0000])[/COLOR][/B] > 0)).Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

Do you need to coerce the Len().... Boolean to a long (in bold red above)?

 

[Rick Rothstein]

Do you need to coerce the Len().... Boolean to a long (in bold red above)?

No... the minus sign in front of the opening parenthesis would do the conversion for you. VB is very accommodating though and will coerce text to values as needed without the programmer needing to do anything... the only reason we need to coerce that part of the code line at all is because True equates to -1 in the VB world and I needed it to be +1 for my expression when True. You can see this automatic coercion with this simple example. Go to a blank sheet and then execute this line of code in the Immediate Window...

Range("A2").Offset(Len(Range("B2").Value) > 0).Select

Since this is a blank sheet, there is nothing in cell B2 and cell A2 is selected (the False result from the Boolean expression is automatically coerced to 0). Now put something in cell B2 and execute it again. Now the (uncoerced) Boolean expression evaluates to True and VB automatically converts it to -1 and, as a result, cell A1 is selected.

 

[d0rian]

Thank you Rick, this appears to do exactly what I need.

I noticed something interesting, by the way -- unrelated to the functioning of the VBA code: one of my frustrations is that up to this point, it has seemed that Excel's Undo function is "disabled" by running any Macro; IOW if I run a Macro of any kind, Excel is unable to Undo anything prior to that Macro having been run (Ctrl-Z just gives an error 'beep'). But I happened to notice that for some reason that 'disabling' doesn't happen with this particular bit of code...i.e. I can take action X (e.g. type 'hello' in cell D1), then run this code, and if I press Ctrl-Z / Undo, it actually WILL undo the 'hello' in D1. Why is that? I.e. is there something about SOME type of VBA that limits the Undo function, while other VBA doesn't?