VBA, Format a Column

  • Hi,

    I have some code that will automatically create the drill down data on multiple pivot tables and extract the data to separate sheets.

    This is gonig to be put in a loop and done for multiple reports, hence not manually clicking into the drill down field.

    My question is as follows:

    I want to auto format each sheet as it is created so that the currency values in a specific column are formated to 2dp .i.e. '199,88.22'.

    I have tried the following code, but ih no effect

    .Columns("E:E").NumberFormat = "#,##0"


    Can anyone correct this code?

    Full code below:

    Thanks in advance

  • Re: VBA, Format a Column


    .UsedRange.Columns(5).NumberFormat = "#,##0.00"

    Note that if you format the entire sheet column, not just the used range then you will massively bloat your file size.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: VBA, Format a Column

    Hi Mattador,

    Just add the decimal places to your existing code, i.e. try changing this line...

    .Columns("E:E").NumberFormat = "#,##0"

    ...to this:

    .Columns("E:E").NumberFormat = "#,##0.00"

    Don't forget the recorder for simple tasks like this as well.



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!