I would like to swap selected cell ranges within the same column without having automatically adjusted attached formulas in other columns. Those cell ranges will almost always be of unequal size.I found a VBA code which does it for two selected cells, but im afraid that this wont help me much. Sub SwapCellsDim sHolder As StringIf Selection.Cells.Count = 2 ThenWith SelectionsHolder =.Cells(1).FormulaIf.Areas.Count = 2 Then ' Cells selected using Ctrl key.Areas(1).Formula =.Areas(2).Formula.Areas(2).Formula = sHolderElse ' Adjacent cells are selected.Cells(1).Formula =.Cells(2).Formula.Cells(2).Formula = sHolderEnd IfEnd WithElseMsgBox 'Select only TWO cells to swap', vbCriticalEnd IfEnd SubI know that another option would be to hold 'shift' when moving the cell ranges (works perfectly fine), but then all the attached formulas will change their reference which I dont want (e.g. If I have a formula referring to cell A1, and im swapping A1 somewhere, the formula will refer to A1's new position, but I want the formula to still refer to A1).I think another option would be to use INDIRECT('G' & ROW) to fix it, but since its a quite resource-intensive formula, Id love to see an alternative.On top of that, the latter two options would not allow me to use tables (which Id prefer for other reasons) because you cant swap cells in tables. This is why Id strongly prefer a VBA option.I hope you can help me, thank you! Maybe it is only necessary to adjust the VBA code a little.Kind regards,MarcoEDIT: If it is significantly easier to swap two equal cell ranges (e.g. Encompassing 5 cells each), then it would also be a good solution.
Press F5 or Ctrl+G on the keyboard to bring up the Go To dialog box. Type in the cell reference of the desired destination in the Reference line of the dialog box. Click on the OK button or press the Enter key on the keyboard. The black box that surrounds the active cell should now jump to the new active cell. Adam needs to swap the contents of two cells and wonders if there is a way to do it without doing a three-step move. There are actually a few.
Sub SwapTwoSelectedRangesDim initialRng As RangeSet initialRng = SelectionIf initialRng.Areas.Count 2 ThenDebug.Print 'Select 2 areas!' Exit SubEnd IfIf initialRng.Areas(1).Cells.Count initialRng.Areas(2).Cells.Count ThenDebug.Print 'The cells should be the same number!' Exit SubEnd IfDim intermediateRng As VariantintermediateRng = initialRng.Areas(1).Cells.Value2initialRng.Areas(1).Cells.Value2 = initialRng.Areas(2).Cells.Value2initialRng.Areas(2).Cells.Value2 = intermediateRngEnd SubSwaping two values is considered an easy task, if you are using an intermediate value. With the ranges, there are two important checks to perform, before swapping them:.
Are the selected areas exactly 2;. Is the number of cells equal in every area;. Then with an intermediateRng as a 3. Variable, the swap is made;. This would only work, if the Areas are per column.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |