Automatically add/hide rows

[wx4usa]

Is there a way to automatically add/show rows that have data? I have a
data entry sheet. Then I have a report. The report pulls data from the
entry sheet. If there is no data for a specific line/row item, is there
a way to automatically hide or not show the row(s) with no data? Thanks

 

[KC Rippstein]

Can you use AutoFilter and show NonBlanks? This is how I set up my reports
that extract data from other data-entry worksheets. The report has
auto-filter enabled, and I just show the filter drop down on column A (I
hide all the other filter buttons on all the other columns to make it
cleaner and prevent any confusion). I just leave column A permanently set
to filter for NonBlanks. When someone pulls up the report, they never see
the blank rows.

 

[wx4usa]

Thanks KC,

Can you use more than one auto filter on a sheet?
As in the example below could you filter each or os there a way to make
it shrink/filter each category?

My example would be a budget as below could have as many as 40 line
items in each main category

Advertising
TV
ROP
MAIL
etc

Sales
Commissions
Clerical
etc.

Operations
Delivery
Warehouse
Salaries
Trucks
Fuel
etc

General and Admin
etc

 

[KC Rippstein]

Auto Filter goes across the top of your worksheet, so all headers are
included.
Generally, you use Auto Filter to narrow your list based on criteria in one
column. If you add a second column to your Auto Filter, then you are taking
an already filtered list and making it even smaller based on your second set
of criteria.
It sounds like you have main categories in column A that should always show,
but then subcategories in column B that you don't want to show if there's no
data for that line in column C (and this assumes you are not putting a zero
but are just leaving it blank). If this is what you're after, then turn on
your Auto Filter for columns A:C and filter column C for NonBlanks.
If you are using zeroes in column C, then you need to set up an Advanced
Filter. The Excel help menu can walk you through that in no time and you'd
just set it up to only show rows where the value in C is greater than zero.

 

[wx4usa]

KC,

I tried the filter and it works, but is there a way to have it
re-expand or unfilter a row when data is added?
I tried this and I could not get it to work. I had to manually
re-autofilter the data.

My empty cell was linked to a data entry sheet on another page. When I
enter data there, Id like the filtered data to change to reflect the
new value

Can you help me? I appreciate it

 

[wx4usa]

KC,
\
How did you hide all of the other filter buttons?

 

[wx4usa]

KC,

Yes column A has Main Categories and sub categories and column B has
dollars.
Should I have Main in A, sub in B and dollars in C?

 

[KC Rippstein]

Yes, I believe your main categories should be a formula adding up all the
values for the subcategories. As a result, the main categories should
always be shown on professional financial statements. I would recommend
moving subcats to B and dollars to C.

 

[KC Rippstein]

I assumed your data entry was on another worksheet. If that's the case,
then the AutoFilter will automatically hide/unhide the appropriate rows as
data is missing/added. You can just always leave it set to show "NonBlanks"
for column C. Just don't bother touching the filter buttons on columns A or
B.

 

[KC Rippstein]

Copy the following code:

Sub HideSomeArrows()
'hide some autofilter arrows
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
Select Case c.Column
' The case numbers below tell Excel which columns to show
' the Auto Filter drop down button for. Typing 1, 2, 5 will
' only show the Auto Filter buttons for Columns A, B, and E.
' Note that every column must have a header, as this process
' stops at the first blank cell in row 1.
Case 3
c.AutoFilter Field:=c.Column, Visibledropdown:=True
Case Else
c.AutoFilter Field:=c.Column, Visibledropdown:=False
End Select
Next
Application.ScreenUpdating = True
End Sub

On your report worksheet (I'll assume it's called Sheet2), right click the
Sheet2 tab and select "View Code". In the dominant window that pops up,
paste this code, press the button on your toolbar that looks like a "Play"
button, and ignore any errors that may or may not pop up.

Then use your windows taskbar at the bottom of your screen to go take a peek
at your worksheet to make sure it turned off the auto filter on columns A
and B. If it was successful, use your windows taskbar to go back to your
VBA code editor and delete the code. We're done with it, so you don't need
to keep it in there.

 

[KC Rippstein]

You might need to revise your "link" to something like this:
=IF('OtherSheet'!C3>0,'OtherSheet'!C3,"")
This way you don't have zero values showing up...you convert those to blanks
using this formula, and then filtering NonBlanks is automatic.
Your main categories will be subtotals of the subcategory values underneath
it. So for C2, =SUBTOTAL(9,C3:C8) using C8 as an example. The last
"overall total" in column C (let's use C50) will be =SUBTOTAL(9,C2:C:49).
Hopefully that does the trick.

 

[wx4usa]

KC,

I still cannot get the autofilter to automatically respond. Can I send
you a small file to look at?
I did reference the othere cell as =IF(Sheet3!A1<>0,Sheet3!A1,"") and
all formatting is set to general on both sheets. If I change cell a1
on the data sheet, it wont show unless I resort. What did I do wrong?

Thanks for your help

 

[wx4usa]

KC,

I still cannot get the autofilter to automatically respond. Can I send
you a small file to look at?
I did reference the othere cell as =IF(Sheet3!A1<>0,Sheet3!A1,"") and
all formatting is set to general on both sheets. If I change cell a1
on the data sheet, it wont show unless I resort. What did I do wrong?

Thanks for your help

 

[KC Rippstein]

You're right, AutoFilter does not auto adjust as the data changes. I am
sorry that I misspoke.