Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide Blank Rows
Hi there,
Does this make sense? Situation: Receive database in client format. I insert a key column, number it sequentially so that I can put it back in order after manipulating the data. I then add a formula on each row, example: =IF(COUNTA(b3:m3)=0,"blank row","") I then sort by this column to put all "blank row" rows together, copy and paste values into another worksheet, fill in pricing, etc., then copy the data back into the original worksheet. After this, I re-sort by the key column, delete the column with the If statement above and the delete the key column to put the data back as sent. Does anyone see a problem with doing it this way? I know I can create a VB macro, but we are trying to avoid this for various reasons. Thanks! -- Thanks! Dee |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide Blank Rows
Is there a reason you copy to a different worksheet?
And if you could pick out a column that always has data on it if that row is used, maybe you could just apply data|Filter|autofilter to that column. Then filter to show the blanks and delete the visible rows. Then remove the filter. This can have trouble under some circumstances. I'd do the the same thing as you but using the original worksheet--with one additional step. I'd convert that formula that returns "blank row" to values before I sorted. The fewer formulas, the faster things work. dee wrote: Hi there, Does this make sense? Situation: Receive database in client format. I insert a key column, number it sequentially so that I can put it back in order after manipulating the data. I then add a formula on each row, example: =IF(COUNTA(b3:m3)=0,"blank row","") I then sort by this column to put all "blank row" rows together, copy and paste values into another worksheet, fill in pricing, etc., then copy the data back into the original worksheet. After this, I re-sort by the key column, delete the column with the If statement above and the delete the key column to put the data back as sent. Does anyone see a problem with doing it this way? I know I can create a VB macro, but we are trying to avoid this for various reasons. Thanks! -- Thanks! Dee -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide Blank Rows
Hi and thanks for your response.
It is done on another sheet as it must then be uploaded into another system that will input pricing automatically and it must be formatted in a specific fashion. It then must be put back into the original worksheet and made to look exactly as it was received before sending back to the client. I like the extra idea of removing the formulas. Thanks - I will implement it. Any other suggestions are welcome. -- Thanks! Dee "Dave Peterson" wrote: Is there a reason you copy to a different worksheet? And if you could pick out a column that always has data on it if that row is used, maybe you could just apply data|Filter|autofilter to that column. Then filter to show the blanks and delete the visible rows. Then remove the filter. This can have trouble under some circumstances. I'd do the the same thing as you but using the original worksheet--with one additional step. I'd convert that formula that returns "blank row" to values before I sorted. The fewer formulas, the faster things work. dee wrote: Hi there, Does this make sense? Situation: Receive database in client format. I insert a key column, number it sequentially so that I can put it back in order after manipulating the data. I then add a formula on each row, example: =IF(COUNTA(b3:m3)=0,"blank row","") I then sort by this column to put all "blank row" rows together, copy and paste values into another worksheet, fill in pricing, etc., then copy the data back into the original worksheet. After this, I re-sort by the key column, delete the column with the If statement above and the delete the key column to put the data back as sent. Does anyone see a problem with doing it this way? I know I can create a VB macro, but we are trying to avoid this for various reasons. Thanks! -- Thanks! Dee -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blank Rows from Merged Cells in Drop Down Menu | Excel Discussion (Misc queries) | |||
Macro to hide blank cells in a range | Excel Discussion (Misc queries) | |||
Can I develop a macro to add blank rows to a list in Excel? | Excel Discussion (Misc queries) | |||
Hide rows but show when printing | Excel Discussion (Misc queries) | |||
how to hide rows in a protected sheet | Excel Worksheet Functions |