Hi Guys,
So I have a VBA macro which I can rearrange data with but need some help to make it more efficient.
So i have a lot of source data (~900,000 rows), populating A2:AD900,000. My code works down column AM which is populated with names. So the code takes the name from AM2 and loops down column I and every time it matches AM2, it copies that row of data and pastes to the right of AM2 until there are no more matched in column I. then it goes to AM3 etc. etc. The problem is I have a lot of names (~400k) but the code only runs in a reasonable time up to about AM50. If there are more it struggles. So i need to make it quicker but am really struggling to add additional things into this code.
So the code is:
Firstly, I want the code to stop looking for more matches of AM2 if it reaches 40 , and move onto AM3 etc. I have tried a couple of things but honestly I don't think I'm even close with putting this in the correct place in the code, let alone the correct commands.
Secondly, to reduce the number of cells the code loops through, I have been trying to re-order the source data alphabetically and then define 'finalrowdata' and a new Dim 'startrow' so that the first letter of the name in AM2 would be defined by 'Dim strLeft As String' and strLeft = Left(str, 1). So if the name was 'Bill Smith' it would recognise 'B' as the first letter then only look in the rows where column I names begin with 'B' (rows 40k-70k for example).
I have tried playing around with the code however I'm really struggling to get anything to work at all as I think the basic structure of the code needs revising and I am out of my depth.
If anyone could help to add these to my code it would be a massive help. Also if anyone has any other ideas to make it more efficient that would be great too. I'm not expecting to do all 400k names in one go but even batches of 5-10k would be much better than the lifetime I'm currently looking at...
Hope this isn't too rambling and makes sense
Thanks
Dave
So I have a VBA macro which I can rearrange data with but need some help to make it more efficient.
So i have a lot of source data (~900,000 rows), populating A2:AD900,000. My code works down column AM which is populated with names. So the code takes the name from AM2 and loops down column I and every time it matches AM2, it copies that row of data and pastes to the right of AM2 until there are no more matched in column I. then it goes to AM3 etc. etc. The problem is I have a lot of names (~400k) but the code only runs in a reasonable time up to about AM50. If there are more it struggles. So i need to make it quicker but am really struggling to add additional things into this code.
So the code is:
Code:
Sub finddata()
Dim name As String
Dim finalrow As Long
Dim finalrowdata As Long
Dim i As Long
Sheets("Sheet1").Range("BI2:CAA5000").ClearContents
finalrow = Sheets("sheet1").Range("AM100000").End(xlUp).Row
finalrowdata = Sheets("sheet1").Range("C1000000").End(xlUp).Row
Dim cell As Range
For Each cell In ActiveSheet.Range("AM2:AM" & finalrow)
For i = cell.Row + 1 To finalrowdata
If Cells(i, 9) = cell.Value Then
Range(Cells(i, 1), Cells(i, 30)).Copy
Cells(cell.Row, Columns.Count).End(xlToLeft).offset(, 1).PasteSpecial xlPasteValues
End If
Next i
Next cell
End Sub
Firstly, I want the code to stop looking for more matches of AM2 if it reaches 40 , and move onto AM3 etc. I have tried a couple of things but honestly I don't think I'm even close with putting this in the correct place in the code, let alone the correct commands.
Secondly, to reduce the number of cells the code loops through, I have been trying to re-order the source data alphabetically and then define 'finalrowdata' and a new Dim 'startrow' so that the first letter of the name in AM2 would be defined by 'Dim strLeft As String' and strLeft = Left(str, 1). So if the name was 'Bill Smith' it would recognise 'B' as the first letter then only look in the rows where column I names begin with 'B' (rows 40k-70k for example).
I have tried playing around with the code however I'm really struggling to get anything to work at all as I think the basic structure of the code needs revising and I am out of my depth.
If anyone could help to add these to my code it would be a massive help. Also if anyone has any other ideas to make it more efficient that would be great too. I'm not expecting to do all 400k names in one go but even batches of 5-10k would be much better than the lifetime I'm currently looking at...
Hope this isn't too rambling and makes sense
Thanks
Dave