Format cells according to the Concatenated number of cells.

[S.H.A.D.O.]

Good afternoon,

I have a macro that outputs number data into columns "A:G", just as a General format.

Now there could be data in all of the columns, or 3 of the columns, or 4 of the columns etc

I have a line of code that concatenates these columns...

    Range("I1:I" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=A1&B1&C1&D1&E1&F1&G1"

...which works perfectly.

At the moment, further down in the Macro I have a line of code...

    Range("I1:I" & Range("A" & Rows.Count).End(xlUp).Row).NumberFormat = "000"

...that formats the Concatenated cells according to the number of columns that has data. In this case the code above would format it as 3, being "000", meaning there are 3 columns with data in. This has to be changed manually within the code each time according to the number of columns used.

My question is, is there a way that I can amend the first line of code I have posted to accomodate this as the data is output from the Macro please. So for 4 columns it automatically formats the Concatenated cells as "0000", for 5 columns it automatically formats the Concatenated cells as "00000" etc So basically it is only formated according to the number of columns used and not for columns that have no data.

Thanks in advance.

 

[mole999]

so something like =LEN(A5)+LEN(B5)+LEN(C5)+LEN(D5) adjust as you require

 

[S.H.A.D.O.]

Thanks mole999,

But where will I incorporate that within the code please?

 

[mole999]

that I hadn't figured, you were after identifying the length of the results, so you could custom format, i was just trying to see if LEN would do that

 

[S.H.A.D.O.]

I have sorted it out, in the end it was quite simple.

Instead of using the line of code...

'   Range("I1:K" & Range("A" & Rows.Count).End(xlUp).Row).Value = _
            Range("I1:K" & Range("A" & Rows.Count).End(xlUp).Row).Value

...I used...

    Range("J1:K" & Range("A" & Rows.Count).End(xlUp).Row).Value = _
            Range("J1:K" & Range("A" & Rows.Count).End(xlUp).Row).Value

...which basically leaves the Concatenate formula intact as a formula as opposed to coping it from a formula to a value.

Thanks to those of you who took the time to look and to post suggestions, it is appreciated.