How do I speed up a CASE statement that loops through a large data set?

nvdunn

New Member
Joined
Nov 4, 2015
Messages
12
Hi there
I have a CASE statement that is looping through a very large data set (>75,000 rows) and if the state says "New South Wales" i need it changed to NSW. I need this abbreviation for all Australian states. My code works but it is taking FOREVER to loop through all the data.
Can i convert my data ranges into an array to speed things up? I'm not sure what to do. Basically if the state says New South Wales I want that same cell changed to NSW. Here's my code:
any help is very much appreciated.
Many thanks :)

---

dim lRowCount as long
Dim i As Long

Application.ScreenUpdating = False

lRowCount = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lRowCount
strState = Application.Worksheets("ClientAddress").Cells(i, 8) 'being where i'm storing my state value


Select Case strState
Case "New South Wales"
Application.ActiveSheet.Cells(i, 8) = "NSW"
'strState = "NSW"
Case "Queensland"
Application.ActiveSheet.Cells(i, 8) = "QLD"
Case "Victoria"
Application.ActiveSheet.Cells(i, 8) = "VIC"
Case "Tasmania"
Application.ActiveSheet.Cells(i, 8) = "TAS"
Case "Western Australia"
Application.ActiveSheet.Cells(i, 8) = "WA"
Case "Australian Capital Territory"
Application.ActiveSheet.Cells(i, 8) = "ACT"
Case "Northern Territory"
Application.ActiveSheet.Cells(i, 8) = "NT"
Case "South Australia"
Application.ActiveSheet.Cells(i, 8) = "SA"

End Select
Next i
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,555
No need to loop!

Try something like:

Columns("H:H").Replace What:="New South Wales", Replacement:="NSW", LookAt:=xlPart, MatchCase:=False

etc
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,261
Office Version
365
Platform
Windows
I wouldn't discount looping as such. On my machine this is about 10 times faster than doing 8 x Find/Replace over 75,000 rows.

Rich (BB code):
Sub Abbrev_States()
  Dim lRowCount As Long, i As Long
  Dim Adr As Variant
  
  lRowCount = Range("A" & Rows.Count).End(xlUp).Row
  Adr = Sheets("ClientAddress").Range("H2:H" & lRowCount).Value
  For i = 1 To UBound(Adr)
    Select Case Adr(i, 1)
      Case "New South Wales": Adr(i, 1) = "NSW"
      Case "Queensland": Adr(i, 1) = "QLD"
      Case "Victoria": Adr(i, 1) = "VIC"
      Case "Tasmania": Adr(i, 1) = "TAS"
      Case "Western Australia": Adr(i, 1) = "WA"
      Case "Australian Capital Territory": Adr(i, 1) = "ACT"
      Case "Northern Territory": Adr(i, 1) = "NT"
      Case "South Australia": Adr(i, 1) = "SA"
    End Select
  Next i
  Range("H2:H" & lRowCount).Value = Adr
End Sub

Edit: Forgot to mention that I have assumed the sheet that is going to receive the results is the active sheet when the code is run. Not sure what that sheet is called.
 
Last edited:

nvdunn

New Member
Joined
Nov 4, 2015
Messages
12
Thank you very much StephenCrump that worked really well! I think I was trying to be too tricky. And it wasn't slow to replace all for all states.
Thanks again :)
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,555
I wouldn't discount looping as such. On my machine this is about 10 times faster than doing 8 x Find/Replace over 75,000 rows.
Thanks Peter, I think if anything you've understated the relative speed advantage of the array approach.

I wasn't expecting .Replace to be that slow, and should have tested!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,261
Office Version
365
Platform
Windows
Thanks Peter, I think if anything you've understated the relative speed advantage of the array approach.

I wasn't expecting .Replace to be that slow, and should have tested!
Cheers Stephen. Probably depends somewhat on the machine I expect. For me, copying the range (I did have about 77,000 rows) from one worksheet to the other then 8 x Find/Replace (ScreenUpdating off) took about 1.6 seconds.
Same data, my code took about 0.18 seconds.
 

nvdunn

New Member
Joined
Nov 4, 2015
Messages
12
Oh good point. I have a relatively fast machine but the user I am creating this macro for has a pretty poor performance machine. I will test it out on his computer. thanks again guys. Much appreciated :)
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top