Copy column from Sheet to another where value <>0

[always_confused]

Hello,

I have two sheets: one with raw data, and one where I'd like to do some analysis. In Sheets("raw_data").Range("W:W") I have measured values. I would like to copy only the non 0 values to Sheets("analysis").Range("A:A") .

The way I'm doing it now is:

Sub copy_data()
Dim lastrow As Long
lastrow = Sheets("raw_data").Range("W" & Rows.Count).End(xlUp).Row    'gets number of rows in raw_data

For i = 2 To lastrow     'copies non null values into A
    If Sheets("raw_data").Range("W" & i).Value <>0 Then
       Sheets("analysis").Range("A"&Rows.Count).End(xlUp).Row.Offset(1,0).Value = Sheets("raw_data").Range("W" & i).Value
    End If
Next i

End Sub

I would just like to know if there's a faster way of doing this, because my raw_data file is quite long....

 

[My Aswer Is This]

Try this:

Sub Filter_Me_Please()
'Modified  4/16/2021  8:59:36 AM  EDT
Application.ScreenUpdating = False
Dim lastrow As Long
Dim c As Long
c = 23 ' Column Number Modify this to your need
lastrow = Sheets("raw_data").Cells(Rows.Count, c).End(xlUp).Row

With Sheets("raw_data").Cells(1, c).Resize(lastrow)
    .AutoFilter 1, "<>0"
        counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("analysis").Cells(1, 1)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[always_confused]

Try this:

Sub Filter_Me_Please()
'Modified  4/16/2021  8:59:36 AM  EDT
Application.ScreenUpdating = False
Dim lastrow As Long
Dim c As Long
c = 23 ' Column Number Modify this to your need
lastrow = Sheets("raw_data").Cells(Rows.Count, c).End(xlUp).Row

With Sheets("raw_data").Cells(1, c).Resize(lastrow)
    .AutoFilter 1, "<>0"
        counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("analysis").Cells(1, 1)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

When I run this I just get the Msg "No values found". I don't quite understand how it works well enough to understand what's going wrong...

 

[My Aswer Is This]

Script looks in sheet named raw_data column W for any cell that does not have a zero entered.
So are we really looking for a zero or a empty cell?

 

[always_confused]

Script looks in sheet named raw_data column W for any cell that does not have a zero entered.
So are we really looking for a zero or a empty cell?

I want to copy values from "raw_data" that are not 0

 

[My Aswer Is This]

I want to copy values from "raw_data" that are not 0

Is the results of zero a result of a formula. And we are looking in column w correct?
How did a zero get in the cell? Was it entered manually or the result of a formula?

 

[always_confused]

Is the results of zero a result of a formula. And we are looking in column w correct?
How did a zero get in the cell? Was it entered manually or the result of a formula?

It's not the result of a formula, it's from a MySQL import. I've tried it with sample sheets and sample data and it works great, but when I apply it to my sheets now it copies the whole "raw_data" sheet into "analysis"

 

[My Aswer Is This]

It's not the result of a formula, it's from a MySQL import. I've tried it with sample sheets and sample data and it works great, but when I apply it to my sheets now it copies the whole "raw_data" sheet into "analysis"

If it works on a sample sheet but not on another sheet then I would not know why.
I assume your running the script from the active sheet which is the sheet with your data in column W

I know nothing about:MySQL import