craigjones1203
New Member
- Joined
- Apr 9, 2021
- Messages
- 2
- Office Version
- 2013
- Platform
- Windows
Hi everyone,
I'm just getting started with VBA and I'm trying to learn how to make my code more efficient. Right now, I need my code to loop through all the rows in one column and apply an "if statement" but Excel crashes whenever I try to run the macro on a large amount of data - it works for 20 rows but isn't scalable to the ~40,000 I need it to work for. Any tips on how to make this more efficient? I copied my (semi- working and very clunky) code below - any advice is appreciated!
Range("L1").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Do Until IsEmpty(ActiveCell.Offset(0, -1))
If ActiveCell.Offset(0, 3) = 0 Then
ActiveCell.Value = ActiveCell.Offset(0, 2).Value & ", " & ActiveCell.Offset(0, 1).Value
Else
ActiveCell.Value = ActiveCell.Offset(0, 3).Value & ", " & ActiveCell.Offset(0, 1).Value & " " & ActiveCell.Offset(0, 2).Value
End If
ActiveCell.Offset(1, 0).Select
Loop
I'm just getting started with VBA and I'm trying to learn how to make my code more efficient. Right now, I need my code to loop through all the rows in one column and apply an "if statement" but Excel crashes whenever I try to run the macro on a large amount of data - it works for 20 rows but isn't scalable to the ~40,000 I need it to work for. Any tips on how to make this more efficient? I copied my (semi- working and very clunky) code below - any advice is appreciated!
Range("L1").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Do Until IsEmpty(ActiveCell.Offset(0, -1))
If ActiveCell.Offset(0, 3) = 0 Then
ActiveCell.Value = ActiveCell.Offset(0, 2).Value & ", " & ActiveCell.Offset(0, 1).Value
Else
ActiveCell.Value = ActiveCell.Offset(0, 3).Value & ", " & ActiveCell.Offset(0, 1).Value & " " & ActiveCell.Offset(0, 2).Value
End If
ActiveCell.Offset(1, 0).Select
Loop