Syntax for formatting a number with a comma for thousands

**HangMan** · 05-30-2013, 05:33 PM

I'm using this code to add a column total, what do I need to add to centre the number in the cell and to format it using a comma for thousands?

        LastRow = Range("D65536").End(xlUp).Offset(2, 0).Row
        Range("D" & LastRow) = "=Sum(D1:D" & LastRow - 1 & ")"
        Range("D" & LastRow).Font.Bold = True
        Range("C" & LastRow) = "Total"
        Range("C" & LastRow).Font.Bold = True

Many thanks

**oeldere** · 05-30-2013, 05:36 PM

If you record it with the macro recorder you will get the code.

**HangMan** · 05-30-2013, 05:52 PM

Hi oeldere,

Okay, I now have this, which works, but is there a more 'efficient' way to write this, with fewer lines of code or is this the best way? I basically want the last row only to be formatted as bold and with the numeric value in column D to include the comma for thousands and be centred, but the word 'Total' in column C to be left ranged.

Many thanks

        LastRow = Range("D65536").End(xlUp).Offset(2, 0).Row
        Range("D" & LastRow) = "=Sum(D1:D" & LastRow - 1 & ")"
        Range("D" & LastRow).Font.Bold = True
        Range("D" & LastRow).HorizontalAlignment = xlCenter
        Range("D" & LastRow).NumberFormat = "#,##0"
        Range("C" & LastRow) = "Total"
        Range("C" & LastRow).Font.Bold = True

**oeldere** · 05-30-2013, 05:56 PM

more 'efficient' way to write this, with fewer lines of code or is this the best way?

Probably there is, but I don't have the solution for that.

But fewer lines are not always a better code.

A code you can understand is more usefull, than a code, which is shorter, but is magic for you.

**HangMan** · 05-30-2013, 05:59 PM

Okay, that's good to know. The code works, which is the key point, so many thanks for your help...

**oeldere** · 05-30-2013, 06:00 PM

Thanks for the reply.

Glad I could help.

**JosephP** · 05-30-2013, 06:01 PM

You can use With blocks but it's not more efficient

        LastRow = Range("D65536").End(xlUp).Offset(2, 0).Row
        With Range("D" & LastRow)
            .formula = "=Sum(D1:D" & LastRow - 1 & ")"
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .NumberFormat = "#,##0"
       End with
      with Range("C" & LastRow)
          .value = "Total"
          .Font.Bold = True
       End with