The VirtualPathProvider is one of the ASP.NET pieces I looked at when 2.0 was new. I suspected then that the VPP would need a little time, but would eventually be the centerpiece of some clever solutions.
Adam Kahtava has documented a number of issues he has with ASP.NET 2.0 Themes. Adam has worked around these issues by using a VirtualPathProvider. See the article descriptively entitled: "A Resolution to The Problems with Themes, Skins, and Cascading Style Sheets (CSS) - Putting the Cascades back into ASP.NET 2.0 Themes (taking control over CSS Cascades / Load Order, Media Types, and Overrides)"
Piyush Shah has devised an approach for sharing a master page across web applications by embedding the shared master page as a resource in an assembly. At run time, the VirtualPathProvider can serve up the master page from the assembly's manifest resource.
The VirtualPathProvider also works magic behind MSDN…