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

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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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





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

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</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.
 
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.

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.


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

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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