I want to know if it is possible give number to column automatically?

[iRasim]

I added excel file to show what I want to.

 

[Asheesh]

@iRasim -

Use the below and drag it...

COLUMNS($A$1:A1)

 

[iRasim]

it doesn't work. it gives numers, but I hide column, numbers become same, no change.Could you do in the file uploaded pls ?

 

[prasaddn]

Hi,

Asheesh's formula works in getting column number. I see you want to count only the visible columns. I am not sure if this is possible with formulaes. It can be done with VBA. The subtotal works in filtered mode for set of row data, not columns.

What is the need for the same? may be we can find alternate solutions without VBA.

Regards,
Prasad DN

 

[iRasim]

Hi, prasaddn thnx for reply. I actually want you mention above. I need give the column number to only visible columns

 

[iRasim]

prasaddn. Is not possible do it without VBA ?

 

[prasaddn]

Some level of good news to you.

You can do it with formulas, without VBA, but everytime you hide or unhide column you need to recalculate your formula by pressing Ctr+F9 keys.

I have used one row to check the width of column. if it is more than 0 that means not hidden, it will count that column. And if width is 0, that means hidden, it will skip to count it.

check the attached file table 2.

Regards,
Prasad DN

 

[Asheesh]

Oops...I misunderstood the question....please ignore my response...

@prasaddn - Nice trick mate....

 

[prasaddn]

for recalculation you need t press F9, not Ctrl+F9. My bad ;(

 

[iRasim]

yeah great. thx a lot.

 

[iRasim]

prasaddn if table begins from column A1 how the formula will be ?

 

[prasaddn]

See the revised file.

regarding =Cell("Width"), Cell formula will return various information about the reference address. The syntax is Cell(Infotype, reference). There are various info type like width, color, contents etc.

Regards,
Prasad DN

 

[iRasim]

Thanks a lot.