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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,891
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,402
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,489
Messages
5,837,634
Members
430,506
Latest member
TonyIbbs

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
Top