I took some time to check it in detail. I created an icon whose images have sizes of 16, 24, 32, 40, 48, 64, 96, 128 and 256. Then I checked which image is shown. All these were done with normal 96dpi. If using a larger DPI, the larger sizes may be used (only checked this a bit in Windows 7). The results:
Windows XP:
Windows 7:
Windows Runtime: (from here)
So the result: Windows XP uses 16, 32, 48-size icons, while Windows 7 (and presumably also Vista) also uses 256-size icons. All other intermediate icon sizes are ignored (they may be used in some area which I didn't check).
I also checked in Windows 7 what happens if icon sizes are missing:
The missing sizes are generated (obviously). With sizes of 16, 32, and 48, if one is missing, downscaling is preferred. So if we have icons with size 16 and 48, the 32 icon is created from the 48 icon. The 256 icon is only used for these if no other sizes are available! So if the icons are size 16 and 256, the other sizes are upscaled from the 16 icon!
Additionally, if the 256 icon is not there, the (possibly generated) 48 icon is used, but not resized anymore. So we have a (possibly large) empty area with the 48 icon in the middle.
Note that the default desktop icon size in XP was 32x32, while in Windows 7 it is 48x48. As a consequence, for Windows 7 it is relatively important to have a 48 icon. Otherwise, it is upscaled from a smaller icon, which may look quite ugly.
Just a note about Windows XP compatibility: If you reuse the icon as window icon, then note that this can crash your application if you use a compressed 256 icon. The solution is to either not compress the icon or create a second version without the (compressed) 256 icon. See here for more info.