Code need to be expanded and running faster

[abjac]

Hi I have below code it change the format to text and the date like this DDMMYY. It works ok.
But if there is blanks or if I set the range so it cover after blanks, it run endless. If there is no blanks and the range is with date it run fast.
SO this is one of the problems, But also I need it to be expanded so it cover all these columns.
k2:K10000
N2:N10000
AR2:AR10000
And in top of that I need it to be able to do this in below sheets.
spain,italy,austria,portugal,france,belgium,netherlands
So I need to be changed for sure. And also the problem with the speed need to be fixed first.

Please have a look I have attached a sheet to work with.

P.s in the test sheet I did not change the sheet name to spain etc. but please do if you want to play with that.

Thanks in advance

Abjac

The code.

Sub DDMMYYSpain()
Dim ws As Worksheet
Dim rCell As Range
Dim rng As Range
Set ws = Sheets("spain")
ws.Select
Set rng = Application.Range("K2:K10000")
For Each rCell In rng
    rCell.NumberFormat = "@"
    rCell.Value = Format(rCell.Value, "DDMMYY")
Next rCell
End Sub

[AB33]

Do not hard coded range, instead use count.

Option Explicit

Sub DDMMYYSpain()
Dim ws As Worksheet
Dim rCell As Range
Dim rng As Range
Set ws = Sheets("spain")
Set rng = ws.Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
For Each rCell In rng
    If Len(rCell) Then
      rCell.NumberFormat = "@"
      rCell.Value = Format(rCell.Value, "DDMMYY")
    End If
Next rCell
End Sub

[abjac]

Hi AB33 and thanks for your answer. That did the trick with the speed and empty cells for sure.
Thanks allot for that
How can the code be changed to include. The 3 columns
k2:K ,N2:N, AR2:AR and take the 7 named sheets.spain,italy,austria,portugal,france,belgium,netherlands

Thanks in advance

Sincerely
Abjac

[AB33]

Using a range will be extremely slow or even crash your PC. Try the attached.

[abjac]

HI AB33 WOW Just checked it and it really works really great. So thanks allot just fantastic code. I managed to get it to work with below 2 codes and one column more. But yours code so wow. Thanks allot

My clumsy but working 2 codes.

Sub RunMacroOnAllSheetsToRight()
   Dim a As Integer

    a = ActiveSheet.Index 'Save current sheet

    For i = a To Sheets.Count
        Call MyFunction(i)
    Next i
   Sheets(a).Activate 'At the end, activate original sheet
End Sub


Function MyFunction(i)
Sheets(i).Activate 'Activate each sheet

    'Code goes here
   Call DDMMYYSpain1
End Function


Option Explicit

Sub DDMMYYSpain1()
Dim ws As Worksheet
Dim rCell As Range
Dim rng As Range
Set ws = ActiveSheet
Set rng = ws.Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
For Each rCell In rng
    If Len(rCell) Then
      rCell.NumberFormat = "@"
      rCell.Value = Format(rCell.Value, "DDMMYY")
    End If
Next rCell
Set rng = ws.Range("L2:L" & Range("L" & Rows.Count).End(xlUp).Row)
For Each rCell In rng
    If Len(rCell) Then
      rCell.NumberFormat = "@"
      rCell.Value = Format(rCell.Value, "DDMMYY")
    End If
Next rCell
Set rng = ws.Range("N2:N" & Range("N" & Rows.Count).End(xlUp).Row)
For Each rCell In rng
    If Len(rCell) Then
      rCell.NumberFormat = "@"
      rCell.Value = Format(rCell.Value, "DDMMYY")
    End If
Next rCell
Set rng = ws.Range("AR2:AR" & Range("AR" & Rows.Count).End(xlUp).Row)
For Each rCell In rng
    If Len(rCell) Then
      rCell.NumberFormat = "@"
      rCell.Value = Format(rCell.Value, "DDMMYY")
    End If
Next rCell
End Sub