Auto hide rows until values are entered

Kagz01

New Member
Joined
Jul 23, 2018
Messages
12
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.
 
Last edited by a moderator:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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?
 
Upvote 0
Try
Code:
If Cells(RowCnt, ChkCol).Value <>"" Then
 
Upvote 0
Try
Code:
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
Hi,
try this

Place both codes in you worksheets code page

Code:
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
 
Last edited:
Upvote 0
Hi,
try this

Place both codes in you worksheets code page

Code:
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top