how would i auto-hide rows when no records exist & auto-unhide when content is pasted into rows of excel?

Question

I have an excel file that's prepared on a per customer basis. It contains what I'll call customer information (contact name, phone number, email address, etc). Below this informaiton on the same sheet, I have a section that varies in row length based on what items the customer is interested in.

I want to setup about 200+ rows on this sheet that are hidden until I paste customer items into (maybe the first row/cell before) the hidden row range. By pasting, the items spill into the once hidden rows/cells causing a percentage of them to appear...leaving the remaining of the orginally 200 hidden rows hidden.

I have spent lots of time looking for a solution, but maybe i'm not searching for the right thing...

Anyone from the Yahoo! community care to give me some advice?

Answer 1

There is no way to do exactly what you want to do without a fairly complicated VBA macro (not one which you could get by using the Record Macro capability).

You might consider how your spreadsheet could be redesigned to eliminate the problem. For example, what about putting all the customer data in one row, rather than multiple rows?

Answer 2

Okay. You gonna hate me for this. For each customer, create a fake =sum() covering all the rows relevent to his. Go to Data>Group and Outline>AutoOutline. Now you can collapse and expand at will by customers.
Try not to hide rows. It only mess up your spreadsheet when copy and paste is applied in correctly.

Answer 3

I agree, it needs to be performed with a Macro... wow... this one replaced into relaxing to do (stressful and nevertheless could desire to artwork on it) working interior the Activesheet (the only yet is that basically works with the 1st column) please artwork with it and in case you improve it permit me be responsive to ;) Sub autohide3() for each rw In ActiveSheet.Cells(SearchRow(), SearchCol()).CurrentRegion.Rows this = rw.Cells(SearchRow(), SearchCol()).fee If this = a million Then rw.RowHeight = 0 final = this next end Sub Public functionality SearchCol() SearchCol = ActiveSheet.Cells.Column end functionality Public functionality SearchRow() SearchRow = ActiveSheet.Cells.Row end functionality ------ replace: w00t!!! i've got made something greater advantageous (macro) this works with a sort of rows and columns decide on the finished rows), sorry I left some words in Spanish: Sub Escondiendo() Dim Columna As Integer Dim Fila As Integer Dim rw As merchandise Dim this As Integer Fila = a million Columna = a million For Fila = a million To determination.Rows.count variety+a million For Columna = a million To determination.Columns.count variety+a million for each rw In ActiveSheet.Cells(Columna, Fila).CurrentRegion.Rows this = rw.Cells(Columna, Fila).fee If this = a million Then rw.RowHeight = 0 next next next end Sub

Answer 4

There is a simple work around for what you asked here. Before i give you a solution, i would like to look at the sheet.

can you pls email me the worksheet and message me on my yahoo messenger so that i can help u live on it. my yahoo id is [e-mail]

Answer 5

I think you may be better off using Access. It would be much easier, safer, convenience, and practical.