我正在寻找一种绕过命令的方法
。删除
。切
在我的 Excel 宏中。我发现这些过程大大减慢了程序速度（最多 20 分钟），因为需要剪切、粘贴和删除的数据更多。此外，这些过程似乎停用了宏的状态栏更新。
如果满足特定条件，我会使用这些命令将一行（第 1 列到第 374 列）的数据移动到不同的工作表中。
以下是我使用这些命令的宏过程：我主要关注的是 350、353 和 357
328 j = 0
329 FinalRowMF = Worksheets("Main Frame").Cells(Rows.Count, 8).End(xlUp).Row
330 FinalRowAr = Worksheets("Archive").Cells(Rows.Count, 8).End(xlUp).Row
331 For k = 7 To FinalRowMF
332 Application.StatusBar = "Ignore = " & Ignore & " Current File " & ImportDate - FirstDateImport + 1 & " of " & LastDateImport - FirstDateImport + 1 & " = " & fileName & " Loop k = " & k - 6 & " of " & FinalRowMF - 6
333 If Worksheets("Main Frame").Cells(k, 6).Value = "" Then
334 j = j + 1
335 End If
336 Next k
337 Worksheets("Archive").Activate
338 If j = 0 Then
339 Worksheets("Archive").Range(Cells(7, 9 + DaysOffset), Cells(FinalRowAr, 9 + DaysOffset)).Interior.ThemeColor = xlThemeColorAccent4
GoTo NoInactives:
340 End If
341 ActiveWindow.ScrollColumn = DaysOffset
342 Worksheets("Archive").ListObjects("Table24").Resize Range("$F$6:NJ" & FinalRowAr + j)
343 With Worksheets("Archive").Range("$I$" & FinalRowAr + 1 & ":NJ" & FinalRowAr + j).Interior
344 .Pattern = xlNone
345 .TintAndShade = 0
346 .PatternTintAndShade = 0
347 End With
348 m = j
349 For n = 7 To FinalRowMF - j
350 Application.StatusBar = "Ignore = " & Ignore & " Current File " & ImportDate - FirstDateImport + 1 & " of " & LastDateImport - FirstDateImport + 1 & " = " & fileName & " Finding and Moving ID " & j - m + 1 & " of " & j & " Loop n = " & n - 6 & " of " & FinalRowMF - 6
351 If Worksheets("Main Frame").Cells(n, 6).Value = "" Then
352 FinalRowAr = Worksheets("Archive").Cells(Rows.Count, 8).End(xlUp).Row
353 Worksheets("Main Frame").Rows(n).Cut Destination:=Worksheets("Archive").Range(Cells(FinalRowAr + 1 - m, 1), Cells(FinalRowAr + 1 - m, 374))
354 Worksheets("Archive").Range(Cells(FinalRowAr + 1 - m, 1), Cells(FinalRowAr + 1 - m, 374)).Borders(xlEdgeTop).LineStyle = xlNone
355 Worksheets("Archive").Cells(FinalRowAr + 1 - m, 8).Borders(xlEdgeTop).ThemeColor = 1
356 Worksheets("Archive").Cells(FinalRowAr + 1 - m, 8).Borders(xlEdgeTop).TintAndShade = -0.249977111117893
357 Worksheets("Main Frame").Rows(n).Delete Shift:=xlUp
358 If m = 0 Then
359 Else
360 n = n - 1
361 m = m - 1
362 End If
363 End If
If m = 0 Then GoTo LastReplaced:
364 Next n
LastReplaced:
365 Application.StatusBar = "Ignore = " & Ignore & " Current File " & ImportDate - FirstDateImport + 1 & " of " & LastDateImport - FirstDateImport + 1 & " = " & fileName & " Loop k Counter Result j = " & j
366 Worksheets("Archive").Range(Cells(7, 9 + DaysOffset + 1), Cells(FinalRowAr, 9 + DaysOffset + 1)).Interior.ThemeColor = xlThemeColorAccent4
367 ArDateChange = ImportDate - 1
368 ArSort = True
GoTo SetMonth:
ArSort:
369 With ActiveWorkbook.Worksheets("Archive").ListObjects("Table24").Sort
370 .SortFields.Clear
371 .SortFields.Add(Range("Table24[[#All],[" & DayValue & " - " & MonthName & "]]"), xlSortOnCellColor, xlAscending, xlSortNormal).SortOnValue.ColorIndex = xlNone
372 .Header = xlYes
373 .MatchCase = False
374 .Orientation = xlTopToBottom
375 .SortMethod = xlPinYin
376 .Apply
377 .SortFields.Clear
378 .SortFields.Add(Range("Table24[[#All],[" & DayValue & " - " & MonthName & "]]"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = 65535
379 .Header = xlYes
380 .MatchCase = False
381 .Orientation = xlTopToBottom
382 .SortMethod = xlPinYin
383 .SortFields.Clear
384 .SortFields.Add(Range("Table24[[#All],[" & DayValue & " - " & MonthName & "]]"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = 49407
385 .Header = xlYes
386 .MatchCase = False
387 .Orientation = xlTopToBottom
388 .SortMethod = xlPinYin
389 .Apply
390 .SortFields.Clear
391 .SortFields.Add(Range("Table24[[#All],[" & DayValue & " - " & MonthName & "]]"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = 230
392 .Header = xlYes
393 .MatchCase = False
394 .Orientation = xlTopToBottom
395 .SortMethod = xlPinYin
396 .Apply
397 .SortFields.Clear
398 .SortFields.Add(Range("Table24[[#All],[" & DayValue & " - " & MonthName & "]]"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = 16711935
399 .Header = xlYes
400 .MatchCase = False
401 .Orientation = xlTopToBottom
402 .SortMethod = xlPinYin
403 .Apply
404 .SortFields.Clear
405 End With
NoInactives:
PS 如果有人也知道如何缩短排序那就太好了。
答案1
一般来说，传输数据的最快方法是通过赋值：
Sheets("Sheet2").Range("A1:C10").Formula =
Sheets("Sheet1").Range("A1:C10").Formula
Sheets("Sheet2").Range("A1:C10").Interior.Color =
Sheets("Sheet1").Range("A1:C10").Interior.Color
请注意，如果您正在分配公式，则无需分配值。
我不明白你要怎样逃脱.delete。
最后，重构电子表格可能更简单，这样就.delete不需要发生这种情况了。