Filter Out Rows As They Become Blank

    I have two spreadsheets. Spreadsheet B reads from the master. No entries are made on spreadsheet B, it is visual only. Changes are made only on the master. I want to create a Macro where all blank lines are filtered out automatically on spreadsheet B, in effect only lines with values are displayed.


    Once a new task is created, the details are typed into the master spreadsheet and spreadsheet B, located in another building, is automatically updated to display the details. Once the task is completed which can be seconds, minutes or hours later, an "x" is placed in column A of the master. I have formulated spreadsheet B is "" all rows where "x" is listed in the master. Towards the end of the day however, I can have 50 visable entries on spreadsheet B spread over 500 rows.


    I want spreadsheet B to display only active tasks from the top of the page down. I currently have those that read the spreadsheet filter out blank rows. Because tasks can be updated at a rate of 10 - 15 per minute with new tasks and the completion of old, I want to eliminate the need for our employees to have to filter themselves.


    Hope I have explained it clearly...any suggestion??

    Re: Filter Out Rows As They Become Blank


    You've got to use VBA for this.


    Record the filtering of sheet B with the Macro recorder. Copy this code into the Worksheet_Change event of the Master sheet.

    Re: Filter Out Rows As They Become Blank


    Welcome to Ozgrid.


    Can you clarify :
    1. Is this two different workbooks or different worksheets on the same workbook ?
    2. How is the data currently copied across ?
    3. You have not filled in your experience level so can you also indicate if you are comfortable with VBA.


    Adding to WinteE's comments, if this is the same workbook, you could use the worksheet change event and check to see if Column A has changed. If so you can run an update on the filter on the viewing page.


    You could use a data query to bring across only the records that do not have an x. In the properties of a query you can set it to refresh every x minutes. This will be the fastest method.


    Post back if you need more details


    HTH
    Carl

    Re: Filter Out Rows As They Become Blank


    WOW, you people are quick!


    1. Is this two different workbooks or different worksheets on the same workbook ?
    They are two different workbooks, named Master and Counter. Master is where the changes are made and counter is visual only.
    2. How is the data currently copied across ?
    Simple "if" formula. @if(a1="x","",a1) and this is copied across to column G and down to row 1000. I typically have 500 - 700 entries per day.
    3. You have not filled in your experience level so can you also indicate if you are comfortable with VBA.
    Rookie but a fairly quick learner. I have only used VBA once or twice but no harm in trying if you have a suggestion. Like I said, simple formula in place that can be reproduced if I screw it up.


    A data query sounds interesting, never used it but willing to give it a go. Will it eliminate rows after they have been completed? I had recorded a macro to filter but it is just as easy to filter as it is to run the macro. How would I put the macro into change as suggested by WinTeE? WinTeE, would I put the macro on the master or counter? It is counter that I want filtered.

    Re: Filter Out Rows As They Become Blank


    Ok why not try a query to start with and see if it fits with what your users need.


    In the Master workbook you need to make sure that the first row of the data range contains a single line of headings which are unique. Then give the range (including the headings row) a name (Insert >Name>Define) before saving the workbook.


    In a blank worksheet on the "Counter" workbook select Data > Import External Data > New Database Query then in the dialogue double click "Excel files" and navigate to the Master file. You should see the named range and be able to select the columns you want. As you follow the wizard you can also specify that Column A "Does not equal" X. In the end you should end up with a query area in the worksheet which when you right click and refresh will update from the other file (open or closed). In the properties (right click) you can set it to refresh on open or every so many minutes.


    Have a go and lets us know how you get on.


    Carl

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login