We help IT Professionals succeed at work.

Excel loop

mcrmg
mcrmg asked
on
60 Views
Last Modified: 2018-10-03
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
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I corrected line 5
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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

Author

Commented:
thanks
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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)

Author

Commented:
Okay, I am back. I think the only thing I see so far is to remove -1 on line 9. thanks
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Yes, you're correct.

Author

Commented:
Thank you very much
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.