2 Methods to Copy Cells Based on Certain Criteria in your Excel Worksheet

Data Recovery, Excel File Recovery, Excel Solutions, File Recovery, Office Solutions, Solutions December 19, 2016

Sometimes you need to copy cells that meeting certain criteria. And here we will introduce 2 useful methods to copy the target cells.

In an Excel worksheet, there will certainly be a lot of data and information. However, those cells contain values that meet certain criterion will separate in different range. As a result, it will be difficult when you need to collect them.

In this worksheet, you need to copy cells that is larger than 350. And if you sort those cells one by one and then copy then, you are sure to spend a lot of time and energy.Example to Copy Cells

Thus, now you can use the two methods below to finish this task.

Method 1: Copy the Filter Result

If the target cells are in a range, you can use the filter feature in Excel. You can set multiple criteria for the column and then copy the filter result.

  1. Place your cursor within the target range.
  2. And then click the button “Sort & Filter” in the toolbar.
  3. Next choose the option “Filter” in the drop-down list.Filter
  4. Now click the button in the column header.
  5. Next choose the option “Number Filters”
  6. In the sub menu, choose the option that you need. Here we will choose the “Greater Than”.Filter Data
  7. In the “Custom AutoFilter” window, set the criteria that you need.
  8. And then click the button “OK”.Custom AutoFilter

Therefore, only the cells meet the criterion will show in the worksheet. Other cells will be hidden.Cells in Range

When the filter process ends, you can perform the copy and paste process.

  1. Select the result in the worksheet.
  2. And then press the shortcut keys “Ctrl + C” on the keyboard to copy the range.
  3. Now click another cell in the worksheet.
  4. Next press the shortcut keys “Ctrl +V” to paste those cells.
  5. And then press the button “Esc” to quit the copy mode.

You can see that only the cells meet with the criteria will be pasted to the new range.Paste

However, you will meet with troubles if you paste to the range that share the same rows with the original range. Here only two numbers are pasted into the new range.Trouble

The reason is actually very simple. In the original range, the cells that don’t meet the criterion are hid. And the whole rows that contain those cells are also hid. Therefore, in the new range, you can only see part of the result. Other values are in the hidden rows. In order to show the result, you can clear the criteria in the filter feature. And then you can see the whole result in the worksheet.

Show Result

Method 2: Use VBA Macro

When those target cells are not in a column, you cannot use the filter feature. And in order to finish this task, now you can use VBA macro.

  1. Press the shortcut keys “Alt + F11” on the keyboard to open the Visual Basic editor.
  2. And then click the tab “Insert” in the toolbar.
  3. After that, choose the option “Module” in the sub menu. Therefore, you have inserted a new module in this project.Insert Module
  4. In this step, copy the following VBA codes into the new module.
Sub Copy_Cells()
  Dim c As Range, i As Integer, j As Integer
  i = 0
  For Each c In Range("B2:B13")
    If c > 350 Then
      Cells(2 + i, 6) = Cells(c.Row, c.Column - 1)
      Cells(2 + i, 7) = c
    End If
    i = i + 1
  Next c
   For j = 13 To 2 Step -1
    If Cells(j, 6) = "" Then
      Range("F" & j, "G" & j).Delete Shift:=xlUp
    End If
  Next j
End Sub

In this macro, we will find the cells that meet the criteria. And next the values will be copied to a new range. When the copy and paste process ends, this macro will delete the blank cells in the new range. In your actual worksheet, you need to change the criteria and the range in the codes according to your actual need.

  1. Here click the button “Run Sub” in the tool bar or press the button “Enter” on the keyboard to run the macro.

Next come back to the worksheet, you will see that the result is already in the worksheet.VBA Result

You can also using VBA macros to find cells that are not in a column. Thus, when you need to copy cells in multiple ranges, you can modify the macro and use it.

A Comparison of the Two Methods

In this table, we will show you the advantages and disadvantages of the two methods.

Comparison

 Copy the Filter Result

Use VBA Macro

Advantages

 If you are not familiar with Excel VBA, you can use this method. You can also use this method to copy cells that are in different ranges.

Disadvantages

 When the cells are separate in different area in the worksheet, you cannot filter the data by this method. Using VBA macros will sometimes generate errors in your worksheet.

Hence, the next time you need to copy cells based on certain criteria, you can choose the most suitable method.

Excel Safety is Cause for Concern

The safety of Excel is very essential for you, and you should pay more attention on the Excel safety. Many users have lost their data and information in Excel due to various reasons. If you cannot guarantee the safety of your files, you are sure to suffer a lot. The result of Excel corruption can be disastrous. Whenever you meet with errors in Excel, you can repair Excel xls data corruption by a useful third-party tool. with this tool at hand, you will never lose your data and information.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word doc damage and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.