如何更快地为此VBA循环（对于每个单元格，复制粘贴）？(How can I make this VBA loop (for each cell, copy paste) faster?)

 我有一段代码占用了大量的实际运行时间。 似乎这个循环实际上使Excel有时没有响应（不是100％肯定这一点，但在我看来，这是我最有可能的罪魁祸首）。 无论如何，我想优化这段代码，所以它不需要这么长时间。 
 
 一些背景： 
 
 编辑： application.screenupdating设置为false 
 表格（1）= RawData 
 表（2）= AreaTable 
 在进入循环之前j = 2 
 rng是包括sheet1列CJ中所有值减去标题的范围 

 在sheet1列中，CJ是我想循环的ComponentNames列表。 对于每个ComponentName，我想过滤列AL并复制粘贴（转置）列AL中的所有可见值（总是至少> 1值）到Sheets（2）。 

 对于每个ComponentName，通常有大约1000-1200个ComponentNames和10-240个值（与我复制粘贴到sheet2的值相同）。 

 
For Each cell In rng
    ComponentName = cell.Value
    RawData.Range("A:CJ").AutoFilter field:=17, Criteria1:=ComponentName
    RawData.Range("AL2", Range("AL2").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible).Copy
    AreaTable.Range("B" & j).PasteSpecial Transpose:=True
    j = j + 1
Next cell
 
 我可以对此循环进行哪些更改以更快地完成此过程？ 

I have a piece of code that is taking up a large amount of the actual runtime. It seems like this loop actually makes Excel unresponsive at times (not 100% sure about this, but this seems to me the most likely culprit when I stepped through the code). Anyways, I want to optimize this piece of code so it doesn't take so long.
 
Some background:
 
EDIT: application.screenupdating is set to false
 Sheets(1) = RawData
 Sheets(2) = AreaTable
 j=2 before entering the loop
 rng is the range including all values in sheet1 column CJ minus the header 

 In sheet1 column CJ is a list of ComponentNames that I want to loop through. For each ComponentName, I want to filter column AL and copy paste (transpose) all the visible values in column AL (there will always be at least >1 value) to Sheets(2).

 There are usually around 1000-1200 ComponentNames and anywhere from 10-240 values (the same values that I'm copy pasting to sheet2) for each ComponentName.

 
For Each cell In rng
    ComponentName = cell.Value
    RawData.Range("A:CJ").AutoFilter field:=17, Criteria1:=ComponentName
    RawData.Range("AL2", Range("AL2").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible).Copy
    AreaTable.Range("B" & j).PasteSpecial Transpose:=True
    j = j + 1
Next cell
 
What changes can I make to this loop to get the process done faster?

原文：https://stackoverflow.com/questions/34024861