Avatar of mcrmg
mcrmg
 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
VBA

Avatar of undefined
Last Comment
mcrmg

8/22/2022 - Mon
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mcrmg

ASKER
Have to fix my boss's pc now....I will close the question now. and post back. thank you
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

Yes, you're correct.
mcrmg

ASKER
Thank you very much