CopyPaste Macro to skip blank cells and zero values OR Delete Blank Cells and zero values

D

Daniel_15

New Member
Joined
Apr 8, 2015
Messages
29
Hi
I am trying to write a copypaste macro that will only paste non-blank or non-zero values (the CopyNonZeroValues macro). The “EXAMPLE_RANGE” the macro is referring to is a worksheet that has a bunch of formula driven outputs. If the formula returns a zero, I have it return/formatted as “”. Based on this I believe the macro is still taking those values. So I tried another macro to clean this up on the “upload” worksheet. However, instead of deleting rows that appear blank, it keeps them. When I test the “blank” cells in column which contains the pasted data I get varying answers. Testing the cell value with a 0=0 returns FALSE but a Len() test returns 0. So that is a bit confusing. There must be a carry over from the original copied data and formula.
Ultimately, I am trying to get one macro that will copypaste data into a new worksheet and not carry over blanks or zero values cells (compress the data so there are not any empty rows) and then write this data to a text file. I have tried the writing to a text file yet because I can’t seem to get by this first step.

Thank you for your consideration.

Here is the code.

Sub CopyNonZeroValues()
' Defines variables
Dim cRange As Range
Set cRange = Range("EXAMPLE_RANGE")
Range("EXAMPLE_RANGE").Copy
Sheets("upload").Range("D4").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True

End Sub

Sub DeleteAllEmptyRows()
Dim SourceRange As Range
Dim EntireRow As Range

Set SourceRange = Application.Sheets("upload").Range("D1:D600")

If Not (SourceRange Is Nothing) Then
Application.ScreenUpdating = False

For I = SourceRange.Rows.Count To 1 Step -1
Set EntireRow = SourceRange.Cells(I, 1).EntireRow
If Application.WorksheetFunction.CountA(EntireRow) = 0 Then
EntireRow.Delete
End If
Next

Application.ScreenUpdating = True
End If

End Sub
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
I assume your data is in a single column.
You can use a clipboard object, like this:
Note: you need to add the reference “Microsoft Forms 2.0 Object Library” (to work with clipboard)

Code: 
[FONT=Lucida Console][color=Royalblue]Sub[/color] a1112370a()
[i][color=Dimgray]'https://www.mrexcel.com/forum/excel-questions/1112370-copypaste-macro-skip-blank-cells-zero-values-delete-blank-cells-zero-values.html[/color][/i]

[i][color=Dimgray]'you need to add the reference “Microsoft Forms 2.0 Object Library” (to work with clipboard)[/color][/i]
[color=Royalblue]Dim[/color] obj [color=Royalblue]As[/color] [color=Royalblue]New[/color] DataObject
[color=Royalblue]Dim[/color] tx [color=Royalblue]As[/color] [color=Royalblue]String[/color]

Range([color=Darkcyan]"A1:A10"[/color]).Copy
obj.GetFromClipboard
tx = obj.GetText

    tx = Replace(tx, vbLf & [color=Brown]0[/color] & vbCr, [color=Darkcyan]""[/color])
    tx = Replace(tx, vbLf & vbCr, [color=Darkcyan]""[/color])
    
    obj.SetText tx
    obj.PutInClipboard
    MsgBox [color=Darkcyan]"Data has been copied to clipboard"[/color]

[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

Data:

Excel 2013
A
11
22
30
420
5
6
70
85
96
100
Sheet2


The result after you paste in a text file:
1
2
20
5
6

You can use the "tx" variable to write the data to a text file.
 
Upvote 0
Hi Akuini

Thank you for your reply. I must admit I don't follow. Yes, all my data is in one column. I am not sure where to add your code to my own or run independently. Here is what I have done but, not surprisingly it doesn't work (my lack of understanding). I also get compile error (user-defined type not defined). Thank you for your help.

---------------------------------------------------------------------------

Sub CopyNonZeroValues()

Dim cRange As Range
Dim obj As New DataObject
Dim tx As String
Set cRange = Range("EXAMPLE_RANGE")
Range("EXAMPLE_RANGE").Copy
obj.GetFromClipboard
tx = obj.GetText

tx = Replace(tx, vbLf & 0 & vbCr, "")
tx = Replace(tx, vbLf & vbCr, "")

obj.SetText tx
obj.PutInClipboard

Sheets("upload").Range("D4").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True

End Sub





Akuini said:
I assume your data is in a single column.
You can use a clipboard object, like this:
Note: you need to add the reference “Microsoft Forms 2.0 Object Library” (to work with clipboard)

Code: 
[FONT=Lucida Console][COLOR=royalblue]Sub[/COLOR] a1112370a()
[I][COLOR=dimgray]'https://www.mrexcel.com/forum/excel-questions/1112370-copypaste-macro-skip-blank-cells-zero-values-delete-blank-cells-zero-values.html[/COLOR][/I]

[I][COLOR=dimgray]'you need to add the reference “Microsoft Forms 2.0 Object Library” (to work with clipboard)[/COLOR][/I]
[COLOR=royalblue]Dim[/COLOR] obj [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]New[/COLOR] DataObject
[COLOR=royalblue]Dim[/COLOR] tx [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]String[/COLOR]

Range([COLOR=darkcyan]"A1:A10"[/COLOR]).Copy
obj.GetFromClipboard
tx = obj.GetText

    tx = Replace(tx, vbLf & [COLOR=brown]0[/COLOR] & vbCr, [COLOR=darkcyan]""[/COLOR])
    tx = Replace(tx, vbLf & vbCr, [COLOR=darkcyan]""[/COLOR])
    
    obj.SetText tx
    obj.PutInClipboard
    MsgBox [COLOR=darkcyan]"Data has been copied to clipboard"[/COLOR]

[COLOR=royalblue]End[/COLOR] [COLOR=royalblue]Sub[/COLOR][/FONT]

Data:
Excel 2013

A

<colgroup><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet2​



The result after you paste in a text file:
1
2
20
5
6

You can use the "tx" variable to write the data to a text file.
Click to expand...
 
Upvote 0
I think I figured out a solution. thank you for your help.

Here the code.

Sub Copy_Paste_Delete()
Dim cRange As Range
Set cRange = Range("EXAMPLE_RANGE")
Range("EXAMPLE_RANGE").Copy
Sheets("upload").Range("D4").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True
On Error Resume Next
With Sheets("upload").Range("OUTPUT_RANGE")
.Value = .Value
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub
 
Upvote 0
Glad you figured it out.:)

I have tried the writing to a text file yet because I can’t seem to get by this first step.
Click to expand...

My code use clipboard object & modify the content as your requirements, so you can send it to a text file directly (without copy-paste it to another sheet first). So something like this:

Code: 
[FONT=Lucida Console][COLOR=Royalblue]Sub[/COLOR] a1112370c()
    [COLOR=Royalblue]Dim[/COLOR] strFile_Path [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] obj [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]New[/COLOR] DataObject
    [COLOR=Royalblue]Dim[/COLOR] tx [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]

    Range([COLOR=Darkcyan]"A1:A10"[/COLOR]).Copy
    obj.GetFromClipboard
    tx = obj.GetText
    Application.CutCopyMode = False
    
        tx = Replace(tx, vbLf & [COLOR=Brown]0[/COLOR] & vbCr, [COLOR=Darkcyan]""[/COLOR])
        tx = Replace(tx, vbLf & vbCr, [COLOR=Darkcyan]""[/COLOR])
        strFile_Path = [COLOR=Darkcyan]"D:\zz\tezt2.txt"[/COLOR] [I][COLOR=Dimgray]'Change to suit[/COLOR][/I]
    
        Open strFile_Path [COLOR=Royalblue]For[/COLOR] Append [COLOR=Royalblue]As[/COLOR] #[COLOR=Brown]1[/COLOR]
        Write #[COLOR=Brown]1[/COLOR], tx
        Close #[COLOR=Brown]1[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 
Last edited:
Upvote 0
As I said in post 2:
Note: you need to add the reference “Microsoft Forms 2.0 Object Library” (to work with clipboard)

And this is how:
1. Open your VBA editor.
2. Click Tools > References.
3. Check the box next to “Microsoft Forms 2.0 Object Library.”
 
Upvote 0
Thank you for the clarification.


Akuini said:
As I said in post 2:
Note: you need to add the reference “Microsoft Forms 2.0 Object Library” (to work with clipboard)

And this is how:
1. Open your VBA editor.
2. Click Tools > References.
3. Check the box next to “Microsoft Forms 2.0 Object Library.”
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
referenced blank cell returned zero (0) as values
Replies
5
Views
440
Peter_SSs
Peter_SSs
edge37
  • Solved
Help modifying a code for filling blanks in selected range with zeroes
Replies
8
Views
283
NoSparks
N
M
  • Question Question
Cell with 0 value showing blank
2
Replies
15
Views
687
mhessnm
M
L
  • Solved
Linking Data on Different Tab and Showing Blank Values Instead of Zeroes
Replies
3
Views
435
Fluff
Fluff
B
  • Question Question
VBA Code to Send Mail to skip blank cells
Replies
1
Views
271
Micron
M

Forum statistics

Threads
1,223,703
Messages
6,173,973
Members
452,540
Latest member
haasro02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top