Search code examples
excelvbacopycriteria

Copy values based on criteria with macro

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
enter image description here

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.

Solution

  • 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