sooty8
09-22-2010, 08:35 AM
Hi
I have submitted a sample sheet with a single sub se below
Sub Join2Gether()
Application.ScreenUpdating = False
Sheets("Sites").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[2])&TRIM(RC[1])"
Selection.AutoFill Destination:=Range("A2:A28"), Type:=xlFillDefault
Range("A29").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-27]C[3])&TRIM(R[-27]C[1])"
Selection.AutoFill Destination:=Range("A29:A65"), Type:=xlFillDefault
Range("A56").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-54]C[4])&TRIM(R[-54]C[1])"
Selection.AutoFill Destination:=Range("A56:A105"), Type:=xlFillDefault
Range("A83").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-81]C[5])&TRIM(R[-81]C[1])"
Selection.AutoFill Destination:=Range("A83:A109"), Type:=xlFillDefault
Range("A110").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-108]C[6])&TRIM(R[-108]C[1])"
Selection.AutoFill Destination:=Range("A110:A136"), Type:=xlFillDefault
Range("A137").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-135]C[7])&TRIM(R[-135]C[1])"
Selection.AutoFill Destination:=Range("A137:A164"), Type:=xlFillDefault
Range("A164").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-162]C[8])&TRIM(R[-162]C[1])"
Selection.AutoFill Destination:=Range("A164:A190"), Type:=xlFillDefault
Range("A191").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-189]C[9])&TRIM(R[-189]C[1])"
Selection.AutoFill Destination:=Range("A191:A217"), Type:=xlFillDefault
Range("A218").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-216]C[10])&TRIM(R[-216]C[1])"
Selection.AutoFill Destination:=Range("A218:A244"), Type:=xlFillDefault
Range("A245").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-243]C[11])&TRIM(R[-243]C[1])"
Selection.AutoFill Destination:=Range("A245:A271"), Type:=xlFillDefault
Application.ScreenUpdating = True
End Sub
My problem is that Column B sets the criteria for the number of rows
for example there could be just one ID number or many as 100 is there anyway the number of rows the ID number covers that all the columns of the sites can automatically fill to the same row and Column A would fill to the correct rows - as the sample file - or do I have to manually change the ranges everytime>
Many Thanks
Sooty 8
I have submitted a sample sheet with a single sub se below
Sub Join2Gether()
Application.ScreenUpdating = False
Sheets("Sites").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[2])&TRIM(RC[1])"
Selection.AutoFill Destination:=Range("A2:A28"), Type:=xlFillDefault
Range("A29").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-27]C[3])&TRIM(R[-27]C[1])"
Selection.AutoFill Destination:=Range("A29:A65"), Type:=xlFillDefault
Range("A56").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-54]C[4])&TRIM(R[-54]C[1])"
Selection.AutoFill Destination:=Range("A56:A105"), Type:=xlFillDefault
Range("A83").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-81]C[5])&TRIM(R[-81]C[1])"
Selection.AutoFill Destination:=Range("A83:A109"), Type:=xlFillDefault
Range("A110").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-108]C[6])&TRIM(R[-108]C[1])"
Selection.AutoFill Destination:=Range("A110:A136"), Type:=xlFillDefault
Range("A137").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-135]C[7])&TRIM(R[-135]C[1])"
Selection.AutoFill Destination:=Range("A137:A164"), Type:=xlFillDefault
Range("A164").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-162]C[8])&TRIM(R[-162]C[1])"
Selection.AutoFill Destination:=Range("A164:A190"), Type:=xlFillDefault
Range("A191").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-189]C[9])&TRIM(R[-189]C[1])"
Selection.AutoFill Destination:=Range("A191:A217"), Type:=xlFillDefault
Range("A218").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-216]C[10])&TRIM(R[-216]C[1])"
Selection.AutoFill Destination:=Range("A218:A244"), Type:=xlFillDefault
Range("A245").Select
ActiveCell.FormulaR1C1 = "=TRIM(R[-243]C[11])&TRIM(R[-243]C[1])"
Selection.AutoFill Destination:=Range("A245:A271"), Type:=xlFillDefault
Application.ScreenUpdating = True
End Sub
My problem is that Column B sets the criteria for the number of rows
for example there could be just one ID number or many as 100 is there anyway the number of rows the ID number covers that all the columns of the sites can automatically fill to the same row and Column A would fill to the correct rows - as the sample file - or do I have to manually change the ranges everytime>
Many Thanks
Sooty 8