Macro speed - copy/paste rows

[Rijnsent]

Hi all,
I'm working on a macro where I have to swap around some rows on a sheet. So I've added some grouping labels in column B, loop though my 150 rows and move blocks in a certain order. Moving means: cut and paste at the top. Roughly speaking, this is what I'm doing (option Explicit is on):

    Dim Sht As Worksheet
    Dim Rw As Long, StartRw As Long, EndRw As Long, MoveAction As Integer, MoveBlock As Integer, i As Integer
    Dim CopyRwStart As Long, CopyRwEnd As Long, PasteRw As Long

' in a loop, determine the start- and endrow, next,
    Set Sht = Worksheets("MY_SHEET")
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    For i = 1 to 10
         'Do something to determine the start- and endrow and pasterow - blocks are max 10 rows, and the total range everything happens in is 150 rows.
         Sht.rows(CopyRwStart & ":" & CopyRwEnd).Cut
        Sht.Cells(PasteRw, 1).rows("1:1").EntireRow.Insert shift:=xlDown
        Application.CutCopyMode = False
    next i
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlSemiAutomatic

In my macro, there are about 10 of those cut-paste actions (want to move some rows up). The main issues I currently bump into:
- when timing the macro, each cut-paste action takes almost a second, which feels way too long. At my colleagues PC, each cut-paste action takes almost 5 seconds. - so on my PC the total macro runs for 10 seconds, on his almost a minute .
- sometimes, a clipboard-warning pops up (guess Windows native), throwing a warning (not stopping the process).

Does anyone here know a solution to speed this up? And does anyone know if I can in VBA somehow avoid that pesky clipboard warning popping up?

 

[kvsrinivasamurthy]

Does you want only values or Values with formatting to be shifted.

 

[Rijnsent]

Does you want only values or Values with formatting to be shifted.

Everything: formats, formulas, etc. Note: there are no filters or groupings on the sheet (in that sense it's a simple straight forward sheet). Your remark reminded me that maybe one "sort" command will be quicker than my 6 cut-paste ones... Will test that.

 

[Rijnsent]

Okay, the sorting makes it much, much faster, but all formulas in my sorted ranges get messed up, so that's not a solution . Tried various versions with different formulas and lots of $ signs, but a couple of formulas kept on getting messed up.

 

[kvsrinivasamurthy]

Update sample data with expected results in xl2bb format.

 

[Rijnsent]

XL2BB throws a 1004 if I try to export it. The sorting goes wrong because of this issue: Redirecting - that is not something I can solve on my sheet, as there are plenty of references to other sheets.

So the my questions remain open: Does anyone here know a solution to speed this up? And does anyone know if I can in VBA somehow avoid that pesky clipboard warning popping up?

 

[Peter_SSs]

The sorting goes wrong because of this issue: Redirecting - that is not something I can solve on my sheet, as there are plenty of references to other sheets.

I'm not sure that you are interpreting the issue correctly (or else I may be not understanding what you are saying).
It is not references to other sheets that cause the problem, but references (using the sheet name) to the sheet the formula is actually on.

Another simple demo is given here. The problem in this example is this formula on Sheet2 contains the Sheet2! reference
=SUMIF(Sheet1!$B$2:$B$7,Sheet2!A2,Sheet1!$C$2:$C$7)

If the formula is re-written without that sheet name ..
=SUMIF(Sheet1!$B$2:$B$7,A2,Sheet1!$C$2:$C$7)
.. then there is no problem with sorting.

 

[Rijnsent]

@Peter_SSs Give this a try, simple formulas that get messed up when I sort it, using the bit of code below (E/F switch). I copy-pasted the correct formulas in the part from row 21 for reference. I've simplified my actual example, as that has the data coming in from other sheets (and is protected, etc). So that's why I moved to cut-paste rows, but that is very, very slow and has some clipboard pop-ups showing (my first post).

Sub DoSortNOw()

    'BUT MESSES UP FORMULAS!!!
    Set Sht = Worksheets("BS")
    OrderBy = "F" 'E Or F
    Sht.Range("A3:G15").Sort key1:=Range(OrderBy & "3:" & OrderBy & "15"), order1:=xlAscending, Header:=xlNo
    Set Sht = Nothing

End Sub

sort_cut_paste.xlsm
	A	B	C	D	E	F
1	BALANCE SHEET for Company Z	2020	2021	2022
2
3	Row 3	1	3	5	5	1
4	Row 4	2	4	6	6	2
5	Total 3+4	3	25	33	7	3
6					8	4
7	Current deferred tax assets	4	6	6	1	5
8	Other current assets; operating	5	7	7	2	6
9	Operating current assets	24	31	43	3	7
10					4	8
11	Total current assets	27	56	76	9	9
12					10	10
13	Non-operating current assets	10	12	12	11	11
14	Total current assets	15	18	18	12	12
15	EXTRA DATA				TEST	ORIGINAL
16	Outside range	20	21	22
17
18
19
20
21	BALANCE SHEET for Company Z	2020	2021	2022
22
23	Row 3	1	3	5
24	Row 4	2	4	6
25	Total 3+4	3	25	33
26
27	Current deferred tax assets	4	6	6
28	Other current assets; operating	5	7	7
29	Operating current assets	24	31	43
30
31	Total current assets	27	56	76
32
33	Non-operating current assets	10	12	12
34	Total current assets	15	18	18
35	EXTRA DATA
36	Outside range	20	21	22
BS

Cell Formulas
Range	Formula
B5,B25	B5	=SUM(B3:B4)
C5,C25	C5	=SUM(C3:C4)+C14
D5,D25	D5	=SUM(D3:D4)+D16
B9:C9,B29:C29	B9	=B7+B8+B14
D9,D29	D9	=D7+D8+D14+D13
B11:D11,B31:D31	B11	=B5+B9

 

[Peter_SSs]

Okay, I understand now what you are saying. I'm not sure if this will help with your actual data, but with that example, take the formula in C5. It is saying to add the values in the two rows above plus the value in the cell 9 rows below. With this particular sort (sorting on column E) you are keeping original rows 3,4,5 together and they become rows 7,8,9. The formula that was in C5, now in C9, is still (quite correctly) adding the the values in the two rows above plus the value 9 rows below (C18).

If you are going to be changing the relative positions but some values are retaining the original positions then you could anchor the fixed values with with absolute references. For example the original C5 formula could be ..
=SUM(C3:C4)+C$14
.. then that formula when moved by sorting to C9 would be ..
=SUM(C7:C8)+C$14
.. still returning the correct result.

I understand that may not be very easy to implement or very robust. A better option might be to use SIMIF(S) using the text in column A but in this particular example not easy because row 14 is "Total current assets", but so is row 11. (Having two rows with identical labels but different values seems very confusing to me)

The upshot for me is I don't see an easy and robust way that is any quicker than your existing Cut/Paste method

 

[Rijnsent]

Hi Peter, thanks for those insights. The thought direction is a good one, but my actual setup is much more complex and keeps throwing circle errors when I sort (even after adding a bunch of $ signs). I'll stick with the cut-paste for now, but my next version will have some "extract the formula references, hardcopy that, sort, use the hardcopy to correct formulas", that's probably faster VBA code than the current 3 cut-paste actions (2 seconds total).