How to bypass the clipboard for Copy-paste Skip Blanks?

[Poniente]

Hi,

I look for VBA code that executes a Copy-paste while skipping any blanks, for large number of cells in an efficient manner (so not going through the range cell-by-cell).

I already gathered two copy paste methods that do not mess up the clipboard:

This VBA can Copy-past values of ranges while bypassing the clipboard:
Range("'[" & DestinationWb & "]Sheet1'!A1:B2").Value = Range("Source").Resize(2, 2).Value

This VBA can Copy-paste ranges while bypassing the clipboard:
Range("SourceCell").Offset(1, 0).Resize(10, 1).Copy Destination:=Range("DestinationCell").Offset(1, 0).Resize(10, 1)

I am not actually sure if the 2nd one completely bypasses the clipboard, but at least my experience is that it does not interfere with other applications using the clipboard at the same time; i.e. if you execute the VBA, and press control V again, nothing happens.

Highly appreciated if you have any inspiration on how to accomplish a 'skip blanks' Copy paste! (Ideally: copy paste skip blanks, values only)

If only it were possible to instruct an Office instance to make the entire clipboard 'local' to that instance

Thanks in advance,
Poniente

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Your first example above is copying a range that is multiple rows and multiple columns. The second example is data in a single column.
What shaped range are you actually trying to copy?

Also, could you please give us a small set of dummy data and the expected results done manually with XL2BB
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[Poniente]

Thanks for your suggestions Peter!
I've updated my profile. Version 2024 was not available as an option to check but I've mentioned it separately .

The VBA should replicate the "Copy - Paste special - Option: 'skip blanks' " command that can be executed in the GUI. I'm looking for a range of 1500 by 30 cells, quite large... that is why cell-by-cell is not an option for me.

Ideally, there is a way to use a local sort of clipboard, similar to how I think Range("SourceCell").Offset(1, 0).Resize(10, 1).Copy Destination:=Range("DestinationCell").Offset(1, 0).Resize(10, 1) uses a local type of clipboard. But I'm open to any fast solution.

Best, Poniente

 

[Poniente]

P.s. Rather off-topic, but I see that you are 300 messages away from having written 2^16 messages... impressive.

 

[Peter_SSs]

I've updated my profile.

Thanks for that.

I'm looking for a range of 1500 by 30 cells, quite large... that is why cell-by-cell is not an option for me.

I assume that is because of the time it would take? If that is so and the values already in the destination range are not the result of formulas where the formulas need to be retained, then you could give this sort of code a try.
My test data was 2000 rows x 40 columns, so a reasonable amount bigger than what you mentioned above. Both the source range and the destination range had approximately half cells with data and half with no data.
The execution time for the code on my (quite old) machine was 0.14 seconds so I think should be fast enough?

If you are testing with real data, make sure you have a backup copy first.

Sub Skip_Blanks_Test()
  Dim a As Variant, b As Variant
  Dim rSource As Range, rDestTopLeft As Range
  Dim i As Long, j As Long, uba2 As Long

  Set rSource = Range("A1:AN2000")  '<- Source range to Copy
  Set rDestTopLeft = Range("BA1")   '<- Top left cell of 'Paste' range
  
  a = rSource.Value
  uba2 = UBound(a, 2)
  With rDestTopLeft.Resize(UBound(a, 1), uba2)
    b = .Value
    For i = 1 To UBound(a)
      For j = 1 To uba2
        If Len(a(i, j)) > 0 Then b(i, j) = a(i, j)
      Next j
    Next i
    .Value = b
  End With
End Sub

 

[Poniente]

Hi Peter,
Indeed, given that my use case indeed is 'values only', this approach is adequately efficient and works.
Gratitute!
Poniente

 

[Peter_SSs]

Cheers. Thanks for the follow-up.

 

[Poniente]

Hi Peter,

It seems I am actually in need of a refinement of your proposed code... the original Excel copy paste values skip blanks, would not overwrite any cells in the destination, but the proposed routine does (in my case) unduly overwrites the target range with 'nothings'.
Unfortunately, the target range is rather heavily populated with formulas... So I wonder if there is a faster alternative for a cell-by-cell write, instead of the much more efficient '.value = b' in your proposed code.

Happy to hear any ideas you may have on this one.

Kinds, Poniente

 

[Peter_SSs]

the original Excel copy paste values skip blanks, would not overwrite any cells in the destination

It does for me.
If I start with this ..

Poniente.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		A33			A10			B30	B17		B17
2									B48
3		A33	A46		A12				B46	B10
4	A29	A47	A21		A2		B22			B9	B17
5				A36			B33	B39		B8	B28
6		A33			A33				B11	B9	B27
7			A24		A41		B45	B38		B36
8	A13		A47	A19	A36		B45
9	A1	A44	A46		A1				B11		B22
10	A19	A19		A37	A3		B14
Sheet5

.. and Copy A1:E10 -> Select G1 -> PasteSpecial - Values - Skip Blanks then I get this (apart from the blue colour). I have manually added the blue as previously non-blank values in those cells have been over-written.

Poniente.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		A33			A10			A33	B17		A10
2									B48
3		A33	A46		A12			A33	A46	B10	A12
4	A29	A47	A21		A2		A29	A47	A21	B9	A2
5				A36			B33	B39		A36	B28
6		A33			A33			A33	B11	B9	A33
7			A24		A41		B45	B38	A24	B36	A41
8	A13		A47	A19	A36		A13		A47	A19	A36
9	A1	A44	A46		A1		A1	A44	A46		A1
10	A19	A19		A37	A3		A19	A19		A37	A3
Sheet5