Auto hide rows until values are entered

[Kagz01]

I have everything set up so that my upc codes pull the data and only Unique codes and their frequency are recorded. Now i need to auto hide the rows between 9 and 23 if column a is blank. The tricky part is that there is a formula in the all of the cells for pulling data. until data is entered on another sheet all rows from 9 to 23 are blank so that it doesnt look stupid. So, I want to hide those rows so that if someone buys only two products i dont have 15 lines of empty data to print. Thanks in advance for any help you can give!

Is there a formula to adjust row height? If so i can use an if formula to reference a cell with the word yes or no and adjust the height accordingly. Thanks again!


EDIT: Or an if statement that coincides with the earlier part of the statement, If (value of the previous formula = "") then Hide the row, if not Unhide the row.

 

[Kagz01]

Ok i got it to hide the rows that i want hidden that dont contain information with this script:

Sub HideRows()
BeginRow = 9
EndRow = 23
ChkCol = 1


For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub


Sub HideRows2()
BeginRow = 9
EndRow = 23
ChkCol = 1


For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value > 1 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub
_______________________________________________________________________________
Now i need it to automatically UNHIDE when the value changes. Is there something that i need to add to the code to make this happen?

 

[Fluff]

Try

If Cells(RowCnt, ChkCol).Value <>"" Then

 

[Kagz01]

Try

If Cells(RowCnt, ChkCol).Value <>"" Then

I tried it as is. It performs well but it does not update as values are changed. Does there need to be some type of page updating code to make it go back and forth as things change?

 

[Fluff]

You could use a Worksheet_Calculate event, but that will trigger whenever any cell on the sheet calculates, which could cause problems.
The other option would be to use a Change event on the other sheet, looking at the cells that affect your formula.

 

[dmt32]

Hi,
try this

Place both codes in you worksheets code page

Private Sub Worksheet_Calculate()
    HideRows
End Sub


Sub HideRows()
    BeginRow = 9
    EndRow = 23
    ChkCol = 1
    
    For RowCnt = BeginRow To EndRow
        With Cells(RowCnt, ChkCol)
            .EntireRow.Hidden = CBool(Len(.Value) = 0)
        End With
    Next RowCnt
End Sub

Calculate event has no parameter to limit the test range in your worksheet and will run each time sheet is calculated which could be an issue.



Dave

 

[Kagz01]

Hi,
try this

Place both codes in you worksheets code page

Private Sub Worksheet_Calculate()
    HideRows
End Sub


Sub HideRows()
    BeginRow = 9
    EndRow = 23
    ChkCol = 1
    
    For RowCnt = BeginRow To EndRow
        With Cells(RowCnt, ChkCol)
            .EntireRow.Hidden = CBool(Len(.Value) = 0)
        End With
    Next RowCnt
End Sub

Calculate event has no parameter to limit the test range in your worksheet and will run each time sheet is calculated which could be an issue.



Dave

Im not too savvy with this stuff. are you saying to make two macros?

 

[Kagz01]

Ok i think i see whats going on. There are formulas in the cell that do not change . Only the value that it produces changes! SOOOOO i need to reference the value changes from another sheet that i am scanning to update the invoice sheet... Is there a way to do that?


So...

When changes are made on sheet B in Range A2:A500 then run macro to show rows that have changed on Page A

 

[dmt32]

Im not too savvy with this stuff. are you saying to make two macros?

You copy BOTH codes to your sheets code page.

The Calculate event is triggered each time a formula value changes & it calls your HideRows code which I have updated & should hide rows if cell is blank otherwise display rows.

Dave

 

[Kagz01]

Or when change is detected in a (cell) on Page B to unhide (row) on page A