Copy values based on criteria with macro

Question

I have 2 columns, one with dates (column A:A, of type dd/mm/yyyy hh:mm) the other with values of a parameter (column B:B), registered at each date. But not all dates have registered values (in which case in column B I will have -9999.

What I need is to copy to other columns (say D:D and E:E) only the cells where there is a value other than -9999 and the correspondent date too. For example:

Example

My data series is pretty long, it can get to 10000 or more lines, so I cannot do this selection “manually”. I would prefer macros, not array formulae, because I want to choose the moment of the calculation.

Look at the AutoFilter method. If you run into trouble, come back and edit your question to show what you've come up with (working or not). Along with your code, include some sample data that does not have to be retyped from an image. — user4039065
See Copy multiple Range from Sheet1 based on a conditionand paste it in Alongside in Sheet2. — user4039065

Brendan Gooden Brendan Gooden · Accepted Answer · 2016-02-24T07:46:29

This code should do what you are looking for. This will copy all rows with a value in column B, into columns D and E.'

Sub copyrows()
Dim RowNo, newRowNo As Long

    RowNo = 2
    newRowNo = 2

    With ThisWorkbook.ActiveSheet

      .Cells(1, 4).Value = "Date"
      .Cells(1, 5).Value = "H_Selected"

          Do Until .Cells(RowNo, 1) = ""

             If .Cells(RowNo, 2) <> "-9999" Then
               .Cells(newRowNo, 4) = .Cells(RowNo, 1)
               .Cells(newRowNo, 5) = .Cells(RowNo, 2)
               newRowNo = newRowNo + 1
             End If

          RowNo = RowNo + 1

          Loop

    End With

End Sub

Copy values based on criteria with macro

1 Answers