求助：简化

shigongzaojia

俺是VBA菜鸟，请大侠们帮忙简化下。。。。先谢之。再线急等。。。{:soso_e153:}{:soso_e183:}

wudixin96

1. Range("g2:k100").ClearContents
   
2.     Range("g4").Consolidate Sources:=Array( _
   
3.         "'单价1'!R15C1:R37C5", "'单价2'!R15C1:R37C5", _
   
4.         "'单价9'!R15C1:R37C5", "'单价10'!R15C1:R37C5", _
   
5.         "'单价11'!R15C1:R37C5", "'单价12'!R15C1:R37C5", _
   
6.         "'单价21'!R15C1:R37C5", "'单价22'!R15C1:R37C5", _
   
7.         "'单价13'!R15C1:R37C5", "'单价14'!R15C1:R37C5", _
   
8.         "'单价23'!R15C1:R37C5", "'单价24'!R15C1:R37C5", _
   
9.         "'单价15'!R15C1:R37C5", "'单价16'!R15C1:R37C5", _
   
10.         "'单价25'!R15C1:R37C5", "'单价26'!R15C1:R37C5", _
    
11.         "'单价17'!R15C1:R37C5", "'单价18'!R15C1:R37C5", _
    
12.         "'单价27'!R15C1:R37C5", "'单价28'!R15C1:R37C5", _
    
13.         "'单价19'!R15C1:R37C5", "'单价20'!R15C1:R37C5", _
    
14.         "'单价29'!R15C1:R37C5", "'单价30'!R15C1:R37C5", _
    
15.         "'单价3'!R15C1:R37C5", "'单价4'!R15C1:R37C5", _
    
16.         "'单价5'!R15C1:R37C5", "'单价6'!R15C1:R37C5", _
    
17.         "'单价7'!R15C1:R37C5", "'单价8'!R15C1:R37C5", _
    
18.         "'单价31'!R15C1:R37C5", "'单价32'!R15C1:R37C5", _
    
19.         "'单价33'!R15C1:R37C5", "'单价34'!R15C1:R37C5", _
    
20.         "'单价35'!R15C1:R37C5", "'单价36'!R15C1:R37C5", _
    
21.         "'单价37'!R15C1:R37C5", "'单价38'!R15C1:R37C5", _
    
22.         "'单价41'!R15C1:R37C5", "'单价42'!R15C1:R37C5", _
    
23.         "'单价43'!R15C1:R37C5", "'单价44'!R15C1:R37C5", _
    
24.         "'单价45'!R15C1:R37C5", "'单价39'!R15C1:R37C5", _
    
25.         "'单价40'!R15C1:R37C5"), Function:=xlSum, TopRow:= _
    
26.         False, LeftColumn:=True, CreateLinks:=False
    
27.    
    
28.     Columns("g:k").EntireColumn.Hidden = True
    
29.     Columns("A:e").EntireColumn.AutoFit
    
30.     Rows("4:54").EntireRow.Hidden = False
    
31.     Columns("AE:AJ").EntireColumn.Hidden = True
    
32. For i=4 to 51
    
33.   With range("a" & i)
    
34.     If  .Value = 0 Then
    
35.       .EntireRow.Hidden = True
    
36.     End If
    
37.   End With
    
38. Next
    

复制代码

中间那段自己改吧，不知道干嘛

shigongzaojia

先谢了，最主要的是中间部分，能否用代码？取的数值是各单价固定位置，但单价个数不定，
Range("g4").Consolidate Sources:=Array( _ "'单价1'!R15C1:R37C5",  "'单价2'!R15C1:R37C5", .。。。。。"'单价n'!R15C1:R37C5"),咋弄呀？再次麻烦了？{:soso_e179:}{:soso_e183:}