-2

I have two columns of data that is pulled into a worksheet from data on other sheets elsewhere in the workbook via a formula in each cell...

The first column, Column A, has either a Yes, No or is blank from data that is pulled in via a formula from another sheet.

The second column, Column B, also has data pulled in from elsewhere but every row has data in it.

What I hope to do is hide any rows that does not have anything in column A. Any rows with data in column A should be visible. I'd like this to be updated via the worksheet_change event using VBA when data is entered that appears in column A.

Many thanks if you can help.

Community
  • 1
  • 1
Andy
  • 1,422
  • 5
  • 27
  • 43
  • Please show us what have you tried? If you haven't tried anything thne [This](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) will get you started. Post back with the code that you tried and we will take it from there.... – Siddharth Rout Sep 12 '15 at 19:23
  • I haven't tried anything. I have used the worksheet_change function to create a timestamp when a cell is updated but beyond that, nothing. – Andy Sep 12 '15 at 19:36
  • I have added my own reply below... – Andy Sep 13 '15 at 09:08

1 Answers1

0
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
        Application.EnableEvents = False
dim lrow as Integer
dim i as Integer
     lrow = Cells(1, 2).End(xlDown).Row
    For i = 1 To lrow
    If Cells(i, 1) = 0 Then
    Rows(i).Select
    Selection.EntireRow.Hidden = True
    End If
    Next
        Application.EnableEvents = True
    End Sub

You have to insert this on the code of the sheet. right click the sheet name and press the view code and save it as macro enable. It gets activated when changes have done to column a.

Balinti
  • 1,524
  • 1
  • 11
  • 14
  • Two problems here... changes are made to column A, every row in B already has data in. And because the data is pulled from elsewhere, it has a formula in every cell. By "hide any rows that does not have anything in column A", I mean any result that isn't numerical above 0. The formula will return a 0 if it isn't returning anything. – Andy Sep 13 '15 at 14:30
  • You wrote in your NP, Yes, No, or blank. Corrected your remarks, try it now. – Balinti Sep 13 '15 at 14:36