0

I am trying to format an entire column as a zip code with VBA. I know this can be done by selecting Special --> Zip Code from the numbers drop down. Is this something that the .NumberFormat function supports in VBA?

I tried this with no luck: 

Sheets("Sheet1").Columns(2).NumberFormat = "Zip Code"
CESBoston
  • 83
  • 1
  • 10

3 Answers3

1

The macro recorder is pretty helpful here:

Sheets("Sheet1").Columns(2).NumberFormat = "00000"

* This format is applicable to US (and some other) zip codes, which have 5 digits and can contain leading zeros.

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • 1
    USA zipcodes only? in Canada, they are in `A1A 1A1` format... – braX Nov 14 '19 at 21:38
  • @braX that is a good point. Although I'm guessing OP is USA from the "Boston" portion. – BigBen Nov 14 '19 at 21:39
  • @braX - I was also thinking about it. In Germany they are also with 5 digits. Can someone from Netherlands with a Dutch Excel give it a try? They have letters there. – Vityata Nov 14 '19 at 21:40
  • but the OP could be using a spreadsheet that has some Canadian locations mixed in (The place I worked at had that happen all the time) – braX Nov 14 '19 at 21:40
  • @braX well if they are in the form A1A 1A1 they will not be affected - they will display as text. Come to think of it, how would there be a special format for a Canadian zip code? It will just be text, right? – BigBen Nov 14 '19 at 21:43
  • Why not only `"0"`? - In order to take countries like Austria into account, which only have 4-digit ZIP codes, a reduced number format, such as ("0000" or even) "0" should suffice if it can be assumed that the ZIP lengths are uniform for each country and don't start with a zero digit. – T.M. Jan 27 '22 at 18:29
  • @T.M. - the assumption that they don't start with a zero doesn't not apply to the US. – BigBen Jan 27 '22 at 18:33
  • Thx for hint; so countries with less digits than five will have leading zeros applying "00000" as NumberFormat. @BigBen – T.M. Jan 27 '22 at 18:36
1

This is what the macro recorder produces:

Sub Makro1()
'
' Makro1 Makro
'

'
    Range("E4").Select
    Selection.NumberFormat = "00000"
End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • @BigBen - true. And you even had some time to format the code a bit better:) – Vityata Nov 14 '19 at 21:33
  • 1
    Haha :) I still think two answers that nudge OP to use the macro recorder are a good "hint hint, you can figure this out" :) – BigBen Nov 14 '19 at 21:34
-1

I had forgotten about using macro recorder and found this through my own searching.

This is what the current macro record produces:

Columns("AD:AD").Select
Selection.NumberFormat = "00000"

The original answer did not work for me.

Sheets("Sheet1").Columns(2).NumberFormat = "00000"
Travis
  • 360
  • 4
  • 17
  • 1
    Try `Columns("AD:AD").NumberFormat = "00000"`. There is [no need to Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Jan 27 '22 at 18:35