excel: macro to hide columns?

[Paola]

a greeting to all the visitors of the newsgroup.

I have a workbook with a lot of sheets. in each worksheet there are columns whose sum (vertical) of the values is equal to zero. for print reasons I hide the columns at sum nothing; I do this for each sheet I have to print.
is it possible to automate the process with a macro?
the macro should then read the column sum per column (and sheet per sheet) and hide the column if the sum is equal to zero.

a true thanks to those who will help me.

 

[Shape]

public sub nascondi_col_somma_0()
for i = 1 to worksheets.count
worksheets(i).activate
for a = 1 to 256
dim myrange as range
set myrange = worksheets(i).range(cells(1, a), cells(65536, a))
answer = application.worksheetfunction.sum(myrange)
if answer = 0 then
if worksheets(i).cells(1, a).value <> "" then
columns(a).select
selection.entirecolumn.hidden = true
end if
end if
next a
next i
end sub


copia il codice e lo incolli nel tuo file excel, per farlo vai su strument->macro->visual basic editor da li vai su inserisci->modulo e all'interno del modulo copi il codice.

oppure secomprimi il file allegato e carichi il file modulo1.bas....
spero di aver capito quel che ti serviva...

buon lavoro

 

[Shape]

Well, I'd say you can close here, given the interest......... .

 

[Er Presidente]

I take advantage of asking for some disappointment.
provided I work a lot with excel, but I have never programmed in macroliguage.
from what I see you used visualbasic, is that the only way to write macros in excel?

I would have a problem to solve and I am doubtful whether to use access (I know it but I would, if possible to use only excel), or, I hoped to use excel macros.

If the script is vb, what does excel do?
launches a runtime or processes it directly?
Is it heavy to use vb in excel?

My problem is a file list management within excel, in case I open a special thread.

p.s.: Thank you for the script, I will use it with pleasure.
:finger:

 

[Shape]

I take advantage of asking for some disappointment.
provided I work a lot with excel, but I have never programmed in macroliguage.
from what I see you used visualbasic, is that the only way to write macros in excel?

I would have a problem to solve and I am doubtful whether to use access (I know it but I would, if possible to use only excel), or, I hoped to use excel macros.

If the script is vb, what does excel do?
launches a runtime or processes it directly?
Is it heavy to use vb in excel?

My problem is a file list management within excel, in case I open a special thread.

p.s.: Thank you for the script, I will use it with pleasure.
:finger:

excel has incorporated the vba (visual basic for application) as the whole office environment (access word etc) in practice also the simple registration of actions in excel (macro registration) creates and writes a vba code inside the file. heavy is not heavy indeed, I think you can interface the vb from the outside, but npon I know how.

in any case I use the vba in autocad and I know to do nice things, in office I arrange therefore if you need to open a new trhead and see what comes out together to qlc other companion of :4410: that you okkupa of office automation.

definitely good comes out.

 

[folle76]

I personally find that if you are able to program in vba and you have to deal with sorting issues, selections, filters etc. it is always better to approach to access.
If this data is then shared between users who do not have access license it is best to use access developer toolkit 2007.
http://www.microsoft.com/downloads/details.aspx?familyid=d96a8358-ece4-4bee-a844-f81856dceb67&displaylang=ena runtime is distributed and so other users do not need licenses.
for the 2003 version of access the developer toolkit is not free. more than a vba access table you export and become an excel sheet. I think that putting myself to do complicated things inside excel with vba leads to generate verbose and unclean code. clear then depends on the type of problem.

 

[atorchia]

shape I saw the punctual answer you gave to paola.
I don't know if you can help me... I was looking for some macro info on the whole and I'm "captured" in this forum.
what I'm trying to do is a link between a tape inside a excel sheet and a autocad file. i.e. when I click on a writing in an excel sheet I open an autocad file and zoom in on a given design area.....I don't know if what I'm asking is possible...or maybe it is but not through macro. .
I thank everyone for any suggestions.
ale

 

[Shape]

shape I saw the punctual answer you gave to paola.
I don't know if you can help me... I was looking for some macro info on the whole and I'm "captured" in this forum.
what I'm trying to do is a link between a tape inside a excel sheet and a autocad file. i.e. when I click on a writing in an excel sheet I open an autocad file and zoom in on a given design area.....I don't know if what I'm asking is possible...or maybe it is but not through macro. .
I thank everyone for any suggestions.
ale

you can with the macros of course, if you look in the autocad vba section I did qlc like that. cmq you can launch autoacd from excel and then consequently make autocad what you want and vice versa of course, both lean on the vba.

 

[atorchia]

Thank you.
I try to search through your autocad posts....

 

[Shape]

Thank you.
I try to search through your autocad posts....

open them a post indicating a little more precisely what you have to do.

 

[walmic]

I just joined this forum and found it immediately
many interesting things (compliments to all); I turn to you because I saw you were a master.
your mini course I have already tried and the examples work.
I however need to fill fields of an autocad block
using data contained in a xls file, and then insert it into a drawing.
(the classic drawing revision table)
Where can I leave?