Excel Help

[mkstainton]

I have two workbooks, where one simply reads data from the other. I want the
read only spreadsheet to automatically filter out blank lines whether through
a macro or some other method. I do not want the users to have to press any
key strokes, I need to have this filter updated automatically as changes are
made to the master.

Example

MASTER READ ONLY
A2: 123456 A2: 123456
A3: 123554 A3: 123554
A4: A5: 111222 (A4 Filtered out)
A5: 111222

When A2 becomes blank, it too would be automatically filters out.

There can be data in A2 through to A500. At various times of the day, the
data will change from BLANK to DATA. Currently, the users run Auto filter to
filter out blank spaces but the data can change about every minute. I want
the users of this spreadsheet to have to stop the need for auto filter.

Any suggestions??

 

[CLR]

Maybe this will help..........

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For r = lastrow To 2 Step -1
If Cells(r, "A") = "" Then
Cells(r, "A").EntireRow.Delete
Else
End If
Next r
End Sub

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Pick an un-used column in the Master, say column Z, and in Z1 enter this
array formula:

=IF(ROWS($1:1)<=COUNTA($A$2:$A$500),INDEX($A$2:$A$500,SMALL(IF($A$2:$A$500<>"",ROW($A$2:$A$500)-MIN(ROW($A$2:$A$500))+1),ROWS($1:1))),"")

and copy down.

Column Z will contain the same data as column A except the blanks will be
squeezed out (Z1 will have A2 if it is not blank)

Then have the ReadOnly sheet link directly to column Z in the Master.


REMEMBER the array formula is entered with a CNTRL-SHIFT-ENTER