PDA

View Full Version : Solved: Changing Ranges



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

p45cal
09-22-2010, 01:11 PM
try:Sub Join2Gether()
Dim RowCount As Long, i As Long, j As Long
With Sheets("Sites")
RowCount = .Cells(.Rows.Count, "B").End(xlUp).Row - 1
j = 1
For i = 2 To RowCount * 10 + 1 Step RowCount
.Cells(i, "A").Resize(RowCount).Formula = "=TRIM(" & Range("B2").Offset(, j).Address(False, False) & ")&TRIM(B2)"
j = j + 1
Next i
End With
End Sub

sooty8
09-23-2010, 06:15 AM
Hi p45cal

Thank you for your help - works brilliantly - I don't whether it is just at this end but had difficulty logging on to the forum keep getting Google 'OOPS - done all the usual getting rid of cookies extra and it has still been a struggle - anyway your help is much appreciated

Regards

Sooty8