Hi Sam, Show
No worries man, a macro can be written for this. I just want to make sure we are not confusing columns with rows here. One more thing. You want this to be automatic. What did you have in mind: If you choose for option 1 then please specify move as cut/paste or copy/paste. Please note that the more column/row references you provide the more accurate the code will be. Best regards,
Hi Zem32619, Welcome to MrExcel!! Try this: VBA Code:
Regards, Robert
Hi Robert. Exactly what I wanted it to do. Thank you so much. Great help. Thanks for letting us know and you're welcome 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. Thus, now you can use the two methods below to finish this task. Method 1: Copy the Filter ResultIf 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.
Therefore, only the cells meet the criterion will show in the worksheet. Other cells will be hidden. When the filter process ends, you can perform the copy and paste process.
You can see that only the cells meet with the criteria will be pasted to the new range. 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. 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. Method 2: Use VBA MacroWhen 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.
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.
Next come back to the worksheet, you will see that the result is already in the worksheet. 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 MethodsIn this table, we will show you the advantages and disadvantages of the two methods.
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 ConcernThe 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 How do I copy a cell value based on a condition?Copy Conditional Formatting Using Paste Special. Select cell B2.. Right-click and copy it (or use the keyboard shortcut Control + C). Select the entire range where you want to copy the conditional formatting (C2:C11 in this example). Right-click anywhere in the selection.. Click on the Paste Special option.. How do you copy an IF function cell?To use this method:. Select a cell within your data.. Use the shortcut CTRL SHIFT L to display the filter buttons.. Filter out any records that you don't want to copy.. Select a cell within your data.. Use the shortcut CTRL A to select all the data.. Use the shortcut CTRL C to copy the data (this will copy visible records only). How do I auto populate data in Excel based on another cell?Click and hold the left mouse button, and drag the plus sign over the cells you want to fill. And the series is filled in for you automatically using the AutoFill feature.
How do you copy cells in Excel if column contains specific value text?Paste Special options. Select the cells that contain the data or other attributes that you want to copy.. On the Home tab, click Copy .. Click the first cell in the area where you want to paste what you copied.. On the Home tab, click the arrow next to Paste, and then select Paste Special.. Select the options you want.. |