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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,977
Office Version
  1. 2016
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,777
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
56,671
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,795
Messages
5,638,372
Members
417,023
Latest member
Zimbo38

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