How To Copy/Cut Row of Data Based on Condition [Excel VBA]

This tutorial will cover how to copy or cut entire row of data and paste it into another sheet based on a condition. The condition in this example, is that the cell value in the column must be greater than zero. We will look at two techniques:

  • For Loop
  • Advanced Filter

Each has its merits.

Dataset

This is our dataset. The worksheet with our data is called In. As per our condition, we will check for cells with value greater than zero in the last column. There are 3 cells with zero value. We don’t want to cut the rows in which these cells exist. .All other rows of data will be pasted into the worksheet called Out. It already has column headers.

For Loop

We will loop over the entire dataset. We will use an If statement to check whether the current cell value in Col G is greater than 0. If it is, we will copy/ cut data from the entire row and paste it into the next available row in the sheet Out. 

Sub Solution_For_Loop()

Dim wsIn As Worksheet, wsOut As Worksheet
Set wsIn = ThisWorkbook.Sheets("In")
Set wsOut = ThisWorkbook.Sheets("Out")

wsOut.Range("A2:G" & wsOut.Rows.Count).Clear

Dim lrowIn As Long
lrowIn = wsIn.Range("A1").CurrentRegion.Rows.Count
Dim lrowOut As Long
Dim i As Long
For i = 2 To lrowIn
    If wsIn.Range("G" & i).Value > 0 Then
        lrowOut = wsOut.Range("A1").CurrentRegion.Rows.Count + 1
        'wsIn.Range("A" & i & ":G" & i).Copy wsOut.Cells(lrowOut, 1)
        wsIn.Range("A" & i & ":G" & i).Cut wsOut.Cells(lrowOut, 1)
    End If
Next i
End Sub

Before we move on the next solution, lets discuss the limitations of the For Loop. As the dataset increases, the operation time will increase. On larger dataset e.g. one hundred thousand rows and one hundred columns, this for loop could even hang.

If you want to maintain the same logic, but increase speed, you could load the dataset into a dynamic array loop over it instead.

But, for smaller datasets, it doesn’t really matter. I will always maintain that you can do almost anything in VBA using a For Loop and If statement. Any other technique is just icing.

Ok. Solution 1 is the best way to Cut and Paste the data. However, if you want to copy and paste the data, then then the best way to do this is Advanced Filter.

Advanced Filter

Advanced filter allows us to filter a data range based on certain criteria and we can choose to either filter within the existing dataset or paste the results in a separate worksheet.

There are 3 elements to Advanced Filter.

  • List Range
  • Criteria Range
  • Copy To Range

The criteria range will include the criteria that we want to filter on. It must include the Header of the Column or Columns that we want to filter on.

For this exercise, we need to create the Criteria range. Let’s create in the same In worksheet. In column J, first lets paste the header name Total Charged, since this is the column with our condition. The header name in the criteria range must match the header name in the list range. In the next row, we need to put in our condition. This will need to go within double quotes.

“>0”

And that’s it for the criteria range. We can add more conditions within this columns or even, more columns with conditions. But, that’s a topic for another video.

And finally, our Copy to Range is the Range we want to paste our results into. This will just be header row of the Output sheet.

In VBA, the code to transfer the results is just one line.

Declare and set range variable to hold all three ranges.

Advanced Filter is a method of the range object. The range that we want to refer to is our List range.

Since we want to copy the results to another sheet, we will choose the FilterCopy option.

Next, specify the criteria range.

And finally the copy to range.

rngIn.AdvancedFilter xlFilterCopy, rngCriteria, rngOut

Full code

Sub Solution_Advanced_Filter()

Dim wsIn As Worksheet, wsOut As Worksheet
Set wsIn = ThisWorkbook.Sheets("In")
Set wsOut = ThisWorkbook.Sheets("Out")
wsOut.Range("A2:G" & wsOut.Rows.Count).Clear

Dim rngList As Range, rngCriteria As Range, rngCopyTo As Range
Set rngList = wsIn.Range("A1").CurrentRegion
Set rngCriteria = wsIn.Range("J1").CurrentRegion
Set rngCopyTo = wsOut.Range("A1").CurrentRegion.Rows(1)

rngList.AdvancedFilter xlFilterCopy, rngCriteria, rngCopyTo
End Sub

Related Posts