While working on a project today, I ran into a problem with data in a spreadsheet that had the names of states fully spelled out instead of abbreviated. A brief search on the internet for a vba macro to do the task did not produce any results in the first page of hits.
Not finding such a macro, I decided to write my own using Select Case . . . End Select, and it worked perfectly!
VBA Macro to Abbreviate State Names
Sub AbbreviateStateNames()
‘Created by Rajesh Gandhi apeopleforhisname.org 4/5/2024
‘State names in column 5
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
lrow = ActiveSheet.Cells(Rows.Count,1).End(xlUp).Row ‘finds how many rows have data in the spreadsheet
For i = 2 To lrow
sn = Cells(i, 5).Value2
Select Case sn
Case “Alabama”
Cells(i, 5).Value2 = “AL”
Case “Alaska”
Cells(i, 5).Value2 = “AK”
Case “Arizona”
Cells(i, 5).Value2 = “AZ”
Case “Arkansas”
Cells(i, 5).Value2 = “AR”
Case “California”
Cells(i, 5).Value2 = “CA”
Case “Colorado”
Cells(i, 5).Value2 = “CO”
Case “Connecticut”
Cells(i, 5).Value2 = “CT”
Case “Delaware”
Cells(i, 5).Value2 = “DE”
Case “Florida”
Cells(i, 5).Value2 = “FL”
Case “Georgia”
Cells(i, 5).Value2 = “GA”
Case “Hawaii”
Cells(i, 5).Value2 = “HI”
Case “Idaho”
Cells(i, 5).Value2 = “ID”
Case “Illinois”
Cells(i, 5).Value2 = “IL”
Case “Indiana”
Cells(i, 5).Value2 = “IN”
Case “Iowa”
Cells(i, 5).Value2 = “IA”
Case “Kansas”
Cells(i, 5).Value2 = “KS”
Case “Kentucky”
Cells(i, 5).Value2 = “KY”
Case “Louisiana”
Cells(i, 5).Value2 = “LA”
Case “Maine”
Cells(i, 5).Value2 = “ME”
Case “Maryland”
Cells(i, 5).Value2 = “MD”
Case “Massachusetts”
Cells(i, 5).Value2 = “MA”
Case “Michigan”
Cells(i, 5).Value2 = “MI”
Case “Minnesota”
Cells(i, 5).Value2 = “MN”
Case “Mississippi”
Cells(i, 5).Value2 = “MS”
Case “Missouri”
Cells(i, 5).Value2 = “MO”
Case “Montana”
Cells(i, 5).Value2 = “MT”
Case “Nebraska”
Cells(i, 5).Value2 = “NE”
Case “Nevada”
Cells(i, 5).Value2 = “NV”
Case “New Hampshire”
Cells(i, 5).Value2 = “NH”
Case “New Jersey”
Cells(i, 5).Value2 = “NJ”
Case “New Mexico”
Cells(i, 5).Value2 = “NM”
Case “New York”
Cells(i, 5).Value2 = “NY”
Case “North Carolina”
Cells(i, 5).Value2 = “NC”
Case “North Dakota”
Cells(i, 5).Value2 = “ND”
Case “Ohio”
Cells(i, 5).Value2 = “OH”
Case “Oklahoma”
Cells(i, 5).Value2 = “OK”
Case “Oregon”
Cells(i, 5).Value2 = “OR”
Case “Pennsylvania”
Cells(i, 5).Value2 = “PA”
Case “Puerto Rico”
Cells(i, 5).Value2 = “PR”
Case “Rhode Island”
Cells(i, 5).Value2 = “RI”
Case “South Carolina”
Cells(i, 5).Value2 = “SC”
Case “South Dakota”
Cells(i, 5).Value2 = “SD”
Case “Tennessee”
Cells(i, 5).Value2 = “TN”
Case “Texas”
Cells(i, 5).Value2 = “TX”
Case “Utah”
Cells(i, 5).Value2 = “UT”
Case “Vermont”
Cells(i, 5).Value2 = “VT”
Case “Virginia”
Cells(i, 5).Value2 = “VA”
Case “Washington”
Cells(i, 5).Value2 = “WA”
Case “West Virginia”
Cells(i, 5).Value2 = “WV”
Case “Wisconsin”
Cells(i, 5).Value2 = “WI”
Case “Wyoming”
Cells(i, 5).Value2 = “WY”
End Select
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Copyright © 2011-2024 by Rajesh Gandhi. All rights reserved.
