How do I exclude cells where the formula returns zero?

Question

My file has four sheets.
From all of them, I want to copy and paste column A (from A:10) (which contains a concat formula) when some other rows are populated and then save into a csv.
All rows from A10 onwards have the concat formula which is then filled in depending on the other columns (the same applies for the other sheets).
I have it currently creating sheet1, and pasting there, then saving as a csv.
However, from the first sheet it looks at, it takes only the first line (but the second line - J11 (and so A11) are populated.
In the other sheets, it is copy and pasting the 2 rows that are populated, but also all the other rows as there are formulas there that return zero.
As I have the .End(xlDown) and technically all the other rows are populated.
I tried an IF statement for the last sheet only as a test, and currently it only copies the first populated line, and not the second (but at least it also doesn't copy all the other cells with zero).
Essentially, for each sheet I'd like to loop through with for example E10 is populated, copy and paste A10 into Sheet1, etc., if E10 is not zero.

Sub Output_test1()
'
' Output_test1 Macro
'

'
    Sheets("Create").Select
    Range("A10", Range("J10").End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets.Add.Name = "Sheet1"
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Assign").Select
    Range("A10", Range("E10").End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").End(xlDown).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Date & Time").Select
    Range("A10", Range("E10").End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").End(xlDown).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Event Type").Select
    Dim rg As Range

    For Each rg In Range("E10").End(xlDown)

        If rg.Value > 0 Then
        End If

        Range("A10").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Range("A1").End(xlDown).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Application.CutCopyMode = False
    
    Next
        
    Sheets("Sheet1").Move
    myTime = Format(Now, ("dd.mm.yy"))
    ChDir "C:\Users\"
    ActiveWorkbook.SaveAs Filename:= _
        "Recruit_" & myTime & ".csv", FileFormat:=xlCSVUTF8, _
        CreateBackup:=False
End Sub

In general you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Mar 31 '21 at 17:52
@cybernetic.nomad This code mostly came from recording a macro to get me most of what I needed. I can rework it based on your post, but apart from that, do you know how I can exclude the cells where the formula returns zero? — katecseabra, Apr 01 '21 at 08:14
`If myCell.value <> 0 then`. Sample data and expected output would be useful — cybernetic.nomad, Apr 01 '21 at 14:19
@cybernetic.nomad Sorry, I'm really trying to get to grips with this, but unfortunately it doesn't seem to loop through each cell in the range - it just copies out the first cell - how can I achieve that? — katecseabra, Apr 01 '21 at 16:02

score 0 · Accepted Answer · answered Apr 01 '21 at 18:24

There is no loop in your code not are you checking any values. I assumed you need to check column J in the source sheet and copy column A to the destination sheet.

This is a possible starting point:

k = 1
For i = 10 to 20
    If Sheets("Source").Range("J" & i).Value = 0 then
        Sheets("Destination").Range("A" & k).Value = Sheets("Source").Range("A" & i).Value
        k = k + 1
    End if
Next i

This only copies the value, not the formula. Not sure how much to explain, comment on the answer if any questions

How do I exclude cells where the formula returns zero?

1 Answers1