Setting data range and speeding up VBA code

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
Hi,

I have some simple VBA code which does not work the way I want. It's probably something simple. First, in the code
Code:
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
I would like to start the row count at row B3 not row B1; tried several options but all returned errors.
Code:
Sub Test2()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To LastRow
Range("M" & i).Value = "dog"
    If Range("M" & i).Value = "dog" Then Range("N" & i).Value = "cat"
Next i
End Sub
Although this code returns an answer, it is very slow. It takes 5 minutes to process only about 20,000 rows. If anyone in the Forum could help move the range starting point to cell B3 and speed up execution time to a couple of seconds that would be great. And BTW, I'm not locked into this code, whatever works best will be used.

Any help would be very much appreciated. Am running this on Excel 2007 and Excel 2016.

Thanks,

-Art
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,414
Office Version
2013
Platform
Windows
Why are you making
Range("M" & i).Value = "dog"
Then asking IF Range("M" & i).Value = "dog" then.....

Why not simply

Code:
Sub Test2()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To LastRow
    If Range("M" & i).Value = "dog" Then Range("N" & i).Value = "cat"
Next i
End Sub
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Since you are setting column M values, there is no need for an If().
Code:
Range("N3:N" & Cells(Rows.Count, "B").End(xlUp).Row) = "cat"
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,414
Office Version
2013
Platform
Windows
This will be quicker....saves looping

Code:
Sub MM1()
Dim lastrow As Long, rng As Range
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
With Sheet1 'change sheet name to suit
    Set rng = .Range("M3:M" & lastrow)
    rng.AutoFilter field:=1, Criteria1:="dog"
    rng.Offset(1, 1).SpecialCells(12).Value = "cat"
    .AutoFilterMode = False
End With
End Sub
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
If you wanted the values in column M and change the others:
Code:
Sub Test()
  Dim i As Long, a
  a = Range("M3:M" & Cells(Rows.Count, "B").End(xlUp).Row)
  For i = 1 To UBound(a)
    If a(i, 1) = "dog" Then a(i, 1) = "cat"
  Next i
  Range("N3").Resize(UBound(a)) = a
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,655
Office Version
2010
Platform
Windows
Here is another macro that you can consider...
Code:
Sub DogToCat()
  With Range("M3:M" & Cells(Rows.Count, "B").End(xlUp).Row)
    .Offset(, 1) = Evaluate(Replace(Replace("IF(@=""dog"",""cat"",if(#="""","""",#))", "@", .Address), "#", .Offset(, 1).Address))
  End With
End Sub
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
Hi Guys,

Thanks so much for the overwhelming response! :) You probably realize that we're not really doing dogs and cats in the VBA example; that was an illustrative example. From what I see, you've cleaned up my crummy VBA code.

I'll try all the suggestions tomorrow. What I really need in this exercise is code that executes in a flash for 2-3 hundred thousand rows of data. Yes, large data set. That's the endgame for this exercise.

Any suggestions for how to get there?

Thanks,

Art
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,414
Office Version
2013
Platform
Windows
I'd try the provided codes first.
I don't think you will need to apply any of the suggestions at the link you provided.
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
Hi Michael M,

I am starting to go through the code suggestions. Your code as posted ran fine. However, I made one necessary change as shown below:
Code:
Sub Test4()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To LastRow
    Range("M" & i).Value = "dog"
    If Range("M" & i).Value = "dog" Then Range("N" & i).Value = "cat"
Next i
End Sub
The code I added being:
Code:
Range("M" & i).Value = "dog"
When I added this, the code took more than 5 minutes to execute.

Any thoughts or suggestions?

Thanks,

Art
 

Watch MrExcel Video

Forum statistics

Threads
1,089,882
Messages
5,410,937
Members
403,335
Latest member
ddaveryos

This Week's Hot Topics

Top