• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto hide rows/columns according to reference

carics

Member
Hello,


I have cut in another topic with this question, but probably it is better if I stop being lazy and write my own in the propper forum.


I am thinking about a "dynamic" table showing the names of the customers in C2:IV2(?) and the product clusters in B3:B1000(?)

With a function I would then determine that if the row has no product cluster info (blank cell), then on clumn A there would be an "X". The same way, if in a column there is no customer name (blank cell) then on row 1 there would be an "X".

NOTE: the list of customer names and product clusters would come from another sheet (a sort of a database).


Example:

There are product clusters in B3:B10 and B12:B20. Then A11 and A21:A65536 would be marked "X" (yes, I still have Excel 2003). In this case the correspondent rows would be automatically hidden. I would not want to manually unhide any of them - if I want to see them I would have to go to the database sheet and enter some value in the correspondent slot.

file - http://www.mediafire.com/?84hze1cj9rwm7vu


With this file I would have a dynamic template that would fit all the ranges.


Thanks in advance.
 

Luke M

Excel Ninja
Here's a start. Right click on sheet tab, view code, paste this in:

Code:
Private Sub Worksheet_Calculate()

Application.ScreenUpdating = False

Range("A:A").EntireRow.Hidden = False

Range("A:A").ClearContents

For i = 1 To 65536

If Me.Cells(i, 2) = "" Then

Me.Cells(i, 1) = "X"

End If

Next

Range("A:A").SpecialCells(xlCellTypeConstants, 2).Select

Selection.EntireRow.Hidden = True

End Sub
Code take about 5-10 seconds to complete, and there probably is a faster method. Hopefully it helps you get started. =/
 

carics

Member
Hi Luke M, thank you very much for the code!


I am trying it but my Excel crashes :S


"Run-time error '1004':

Select method of Range class failed"


I thought it would be because the range was too big and I decreased it from 65536 to 500 just to test but I only got the same error... I am copying the entire code from "Private..." till "...End Sub". Something wrong?


again thanks a lot
 
Top