Excel loop

mcrmg
mcrmg used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
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 LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I corrected line 5
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Or...
Dim lr As Long
Dim varCols As Variant
Dim intCol As Integer

varCols = Array("A", "H", "K", "U") ' ETC

lr = ActiveSheet.UsedRange.Rows.Count

For intCol = 0 To UBound(varCols) - 1
    For Each cel In Range(varCols(intCol) & "2:" & varCols(intCol) & lr)
    '...
    Next
Next

Open in new window

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

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 LissOlder than dirt
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 LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Yes, you're correct.

Author

Commented:
Thank you very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial