If a cell is blank, make no changes-CodePudding

I'm basically brand new to VBA/Macros so bear with me.

I'm trying to build a Macro that can be reused on weekly spreadsheets which always have the same headers but variable data in week by week.

I am currently trying to format phone numbers to add area codes, only if the phone number doesn't already have an area code. I've used a large range as the number of rows we would have week by week is variable.

The code I have is working, however it is adding 64 to blank cells too.

Any advice on how I can do this without a range so the change is only affected to cells that actually have data in them, or perhaps a statement where I can basically say 'but if the cell is blank, make no change'?

Sub Change_Mobile_Format()

For Each r In Range("D2:D1000")
If Not (Left(r.Value, 2) = "64") Then
    r.Value = "64" & r.Value
End If
Next r

End Sub

CodePudding user response：

Good Question, although this would be an easy question to find answer to online. I'm sure a similar has been asked. See below, r.value <> "" .

<> means does not equal.

For Each r In Range("D2:D1000")
If Left(r.Value, 2) <> "64" and r.value <> "" Then
    r.Value = "64" & r.Value
End If
Next r

End Sub

I would encourage you to look into doing the data modification in arrays to considerably speed up code. If you decide to try, come back if you're running into problems!