How to Make Loop More Efficient - Crashes

craigjones1203

New Member
Joined
Apr 9, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. 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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
It would help if you told us how your data is laid out (I wondering about what that first loop is doing). Also, what is in the cells in Columns K:N (I think that is the columns you are investigating)... constants or formulas? Are there any blank cells (nothing in them) in Column L? If so, just at the beginning(?) and after last data cell or are there blank cells within the data itself?
 
Upvote 0
Try not to select.

Hope I have the offsets right

VBA Code:
Dim rownum as long

rownum = 1

Do Until cells(rownum, 12) = "" 
rownum = rownum + 1
Loop

Do Until cells(rownum, 12 - 1) = "" 
If cells(rownum, 12 + 3) = 0 Then
cells(rownum, 12) = cells(rownum, 12 + 2 & ", " & cells(rownum, 12 + 1)
Else
cells(rownum, 12) = cells(rownum, 12 + 3 & ", " & cells(rownum, 12 + 1) & " " & cells(rownum, 12 + 2)
End If
rownum = rownum + 1
Loop
 
Upvote 0
Hi & welcome to MrExcel.
If I've understood your code correctly, maybe
VBA Code:
Sub craigjones()
   With Range(Range("L" & Rows.Count).End(xlUp).Offset(1), Range("K" & Rows.Count).End(xlUp).Offset(, 1))
      .Value = Evaluate(Replace(Replace(Replace("if(@3=0,@2&"", ""& @1 ,@3&"", ""&@1&"" ""&@2)", "@3", .Offset(, 3).Address), "@2", .Offset(, 2).Address), "@1", .Offset(, 1).Address))
   End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top