Setting data range and speeding up VBA code

[artz]

Hi,

I have some simple VBA code which does not work the way I want. It's probably something simple. First, in the 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.

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

 

[Michael M]

Why are you making
Range("M" & i).Value = "dog"
Then asking IF Range("M" & i).Value = "dog" then.....

Why not simply

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]

Since you are setting column M values, there is no need for an If().

Range("N3:N" & Cells(Rows.Count, "B").End(xlUp).Row) = "cat"

 

[Michael M]

This will be quicker....saves looping

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]

If you wanted the values in column M and change the others:

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]

Here is another macro that you can consider...

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]

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

 

[artz]

Everyone,

Just happened to find this link:

https://analystcave.com/excel-improve-vba-performance/

Don't which suggestions might apply.

-Art

 

[Michael M]

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]

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:

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:

Range("M" & i).Value = "dog"

When I added this, the code took more than 5 minutes to execute.

Any thoughts or suggestions?

Thanks,

Art