Having Issue Looping Through a Large Range in Excel

user_13262679 0
Viewed 62

This code loops through a given range and converts each value to the short month name. It works great on small ranges but I wrote it for a dataset I get on a regular basis that is well over 100,000 rows. It still works on the full range but it takes a very long time and looks like it crashes but if I wait about 5 minutes it works.

Any ideas on how I can improve this?

Sub ConvertToMonth()

Dim selectedRange As Range

Set selectedRange = Application.Selection

Application.ScreenUpdating = False

For Each Cell In selectedRange
       Cell.Value = MonthName(Cell.Value, True)
Next

Application.ScreenUpdating = True

End Sub
vba excel
3 Answers

You don't need to loop here at all. Just apply the format to the entire range all at once

SelectedRange.NumberFormat = "mmm"
user_67064190

Except if you really need to do this in VBA, I think it's better for you to use the Excel build-in functions. In the screenshot below, I used the following formula for C3 to C31 to get the Short name of a giving date month:

=TEXT(A3,"MMM")

enter image description here

It is very quick and avoid VBA's complications if designing it is still a challenge.

Let me know if this meets your need!

NB: what I saw as dangerous with your VBA Function as well is that It is Destructive, I mean you have no way to Undo it after you fire it up and it is overwritting the selection regardless of its content. However, from Excel, you still can undo it. But it's up to you

user_73581880

Your issue is with the read/ write speed to the front end range in Excel. The best way to approach handling large data sets is to use an array. First, store your data set to an array, manipulate the array, then output the array. Much like what I have done below:

Sub ConvertToMonth()

    Dim selectedRange As Range
    Dim arr() As Variant, i As Long

    Set selectedRange = Application.Selection

    arr = selectedRange.Value

    Application.ScreenUpdating = False

    For i = LBound(arr,1) To UBound(arr,1)
       arr(i,1) = MonthName(arr(i,1), True)
    Next i

    selectedRange.Value = arr

    Application.ScreenUpdating = True

End Sub
user_98702110
Related