asked on

Excel loop

Hi,

Is there a way to enhance the code EE helped me by looking at specific columns.
https://www.experts-exchange.com/questions/29120476/formatting-data.html

Sub FormatDate()
    Dim cel As Range
    
    For Each cel In Range("A2:C" & ActiveSheet.UsedRange.Rows.Count)
        If InStr(1, cel.Text, ".") = 0 And InStr(1, cel.Text, ",") = 0 Then
            If Len(cel.Value2) > 5 Then
                If Not IsDate(cel.Value2) Then
                    cel = Left$(cel.Value2, 4) & "/" & Mid$(cel.Value2, 5, 2) & "/" & Right$(cel.Value2, 2)
                End If
            End If
            cel.NumberFormat = "mm/dd/yyyy"
        End If
    Next
    
End Sub

Open in new window

I just found out there are just way too many columns I need to check.
E, H, K, U, V, AC, AP, AQ, AR, BF, BM...................

Is there a way to loop through those columns?
thanks

Martin Liss

Dim rng As Range
Dim lr As Long

lr = ActiveSheet.UsedRange.Rows.Count
Set rng = Union(Range("A2:A" & lr), Range("H2:H" & lr), Range("K2:K" & lr), Range("U2:U" & lr)) 'ETC
For Each cel In rng
...
Next

Open in new window

Martin Liss

I corrected line 5

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mcrmg

ASKER

Have to fix my boss's pc now....I will close the question now. and post back. thank you

mcrmg

ASKER

thanks

Martin Liss

I could have waited until you tested so let me know if there are problems, but in any case you’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
Experts Exchange Top Expert VBA (current)

mcrmg

ASKER

Okay, I am back. I think the only thing I see so far is to remove -1 on line 9. thanks

Martin Liss

Yes, you're correct.

mcrmg

ASKER

Thank you very much