| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 71892 | 2006-08-21 00:36:00 | Excel: insert macro in cell formula | tinakarori (5695) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 479763 | 2006-08-21 00:36:00 | Can someone 1. confirm whether it is possible to insert a macro in a formula such that if a particular condition is fulfilled, the macro runs automatically; and, 2. explain step by step how to do this, please? The specific use is to hide rows in a list downloaded to Excel, where the amounts in some rows are zero. TIA Tinakarori |
tinakarori (5695) | ||
| 479764 | 2006-08-21 01:13:00 | It's probably better to run a macro after the file is downloaded. More information is needed tho, 1. Is the file always downloaded into the same workbook? 2. How is it downloaded - manually of via a macro? 3. Are the zero numbers always in the same column or can they be in any column and if in any column that row is to be hidden. |
rad_s4 (7401) | ||
| 479765 | 2006-08-21 01:39:00 | I can't insert the formula and macro until after the data itself has been downloaded into Excel - see 2. below. 1. The download wouldn't be done very often - say 5 or 6 times per year. Each download would probably be done to a new worksheet in one workbook. NB: While this job doesn't have to be done often, each download is well over 10k rows - hence my search for a way to automate deleting rows with zero numbers. 2. The download itself has to be done via a utlity function in the source database, so no macro needed for that step. 3. Yes, the zero numbers will always be in a specific and unchanging column. Cheers Tinakarori :thumbs: |
tinakarori (5695) | ||
| 479766 | 2006-08-21 01:42:00 | Can you say which column the zeros are in, and also will there be any blank cells in that column? |
rad_s4 (7401) | ||
| 479767 | 2006-08-21 01:51:00 | Column A has blank cells corresponding to cells in Cols D and F that have zero amounts in them . Thus, I suppose on further thought, a macro could simply delete any row in the table that has a blank in Col A, i . e . the macro wouldn't necessarily have to be embedded inside a formula at all . However, how would the macro be set to stop deleting blank rows once it got to the bottom of the table? Perhaps it would be better that the macro checked the cells in Cols D and F of each row, starting from Row 1, and only deleted the row if cells D and F contained zero? The macro should then automatically stop once it got to the bottom of the table, i . e . to a row that contained a blank in both Cols D and F . . . . . . . . . . . . . . . . ? |
tinakarori (5695) | ||
| 479768 | 2006-08-21 02:13:00 | Whoa!!!! :horrified Deleting rows is a whole different ballgame to Hiding rows. Please clarify. |
rad_s4 (7401) | ||
| 479769 | 2006-08-21 02:47:00 | The following macro will delete all rows which contain blanks in column A. If a cell appears blank but contains a space character the row will not be deleted. Sub Delete_Blanks() Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub HTH |
rad_s4 (7401) | ||
| 479770 | 2006-08-21 03:27:00 | Whoa!!!! Deleting rows is a whole different ballgame to Hiding rows . Please clarify . Sorry - I *did* originally say Hide, but given that the zero amount rows do not contain any data, they might just as well be deleted - so simplifying the format of the table for e . g . printing, and also minimising the file size . Thanks for the piece of code - I'll try it out shortly . Cheers Tinakarori |
tinakarori (5695) | ||
| 1 | |||||