The code must go in the Worksheet code module of the Worksheet of interest, so not in a normal code module.
The simplest way I can think of doing this is to use an Event triggered code The code below may get you started. Now on the selected rows to be hidden in coding, if I select E14 as 'Vendor' it should hide E15 to E17. Hi, I am new to VBA, I wrote a code to hide multiple rows(14 to all the way until 81) based on a cell value.
I suspect each copied macro is somehow analysing/referring to the original, but i cant see how that would be the case. For obvious reasons, i haven't took it much farther. When there's 3, it takes nearly 4 minutes. When there is only one materials list in the workbook it takes 29 seconds to run the 'hide' Macro ( approx 250 rows) When there is 2 materials list in the workbook it takes 90 seconds to run the 'Hide' macro. When the materials list is copied to an new worksheet the Show/Hide macros slow considerably eg. This also copies the buttons attached to the titles. I have another macro entitled 'database' which copies the Master materials list into a separate worksheet if required and activated. On the title section of each material there are 2 buttons, a show and Hide. Hi, I have Used the code below to hide rows of Materials based on the quantities in Column 1. Sub HideRows() BeginRow = 1 EndRow = 100 ChkCol = 3 For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value. The following macro will examine a particular cell in the first 100 rows of a worksheet, and then hide the row if the value in the cell is less than 5. You can, however, achieve the same effect by using a macro to analyze the cell and adjust row height accordingly. There is no way, unfortunately, to easily hide rows based on the value of a particular cell in a row. Replace the line colors with the ones you want to use.Įxcel provides conditional formatting which allows you to change the color and other attributes of a cell based on the content of the cell.Using cell range B2:C10, find and replace delete with blanks.There should be no formula left in cell range B2:10. Select cell range B2:C10, copy and paste as value. Type a number, Excel treats it as a number - unless it looks like a date or a fraction. Type text in a cell, Excel will treat it as text. When using General format, Excel formats cells based on its internal rules on a cell-by-cell basis. Select cells B2 and C2 and paste them to the rest of the rows (B3:C10).Replace the values in B2 and C2 by pasting the formula above in their respective columns.Just tweak the formula to adapt if there's no change in the value. For 'Down' column, it's just the opposite value checking. If it is a number, for 'Up' column formula, it checks the current checked cell's value if it is greater than the value above it or less than the value below and display that value if true or delete if not. I used the following table in this workaround (starting from A1), filled B2:C10 with zeroes and created a line chart with values from cell range B2:C10: Please note that I used the following formula for 'Up' column and 'Down' column, respectively: =IF(COUNT($A1)>0, IF(OR($A2$A1), $A2, 'delete'), IF($A20, IF(OR($A2>$A3, $A2$A3, $A2, 'delete')) What the formula does is check first if the value above the current checked cell is a number.
0 kommentar(er)
