Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Auto Hide & Unhide summary sheet thru VBA

  Asked By: Jodon    Date: Nov 13    Category: MS Office    Views: 2973
  

I have two sheets in a file (Excel 2000) with following names "Data" &
"Summary".
In sheet with name "data" there are 4 columns (Sr, Description, Expense
Type & Amount), and there are number of records in it.
In sheet with name "Summary" there are 3 columns (Sr, Expense Type &
Amount). Now want to show only summary by each "Expense Type" .
And those "Expense Type" which have no amount or zero balance should
automatically hide (should hide/unhide automatically & without clicking a
button) & show Sr number automatically where amount is not equal to zero).

Any help???

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Minal Nayak     Answered On: Nov 13

The easiest way would be to set up autofilters on the columns and
set it to "nonblanks"
or "custom" and set to Greater Than 0.
If you're concerned that a user may turn off the filters, or change them,
you can record a macro to turn on the autofilters, and set it the way
you want it, then copy the macro into a change event.

if the Summary tab updates automatically from the the data on the "data" tab,
then you can set the filter in the worksheet_activate event.

How do you WANT it to act?

 
Answer #2    Answered By: Haru Tanaka     Answered On: Nov 13

I was already doing it thru auto  filter & Data>Consolidation some times,
but want thru VBA (as I m new to VBA & want to learn as it saves a lot of
time) just to automate the sheet.

 
Answer #3    Answered By: Jacob Evans     Answered On: Nov 13

OK... there's probably HUNDREDS of different approaches and variations
to approaches to do what you're asking.
In order to recommend an approach, I REALLY need to understand
how you WANT it to act.

Here's the clues I'm working with:

You said: "In sheet  with name "Summary" there are 3 columns (Sr, Expense Type &
Amount). "

and: "those "Expense Type" which have no amount or zero balance should
automatically hide  "

I believe this to mean:
Lines where the VALUE in the "Amount" column is blank or 0 should be hidden.

You said: "(should hide/unhide automatically & without clicking a button)"

I believe this to mean:
Data is changed in the "Data" tab. When the "Summary" sheet is selected,
the data is automatically updated, and the blank or 0 value lines will be
hidden.
=====================================================
Now, if this is an accurate statement of the "problem", then it's relatively
easy to
do.
First though, do you already have the "Summary" sheet working to summarize
the data from the "data" sheet?
If so, then the first hurdle is done and we're ready to move on.

How much VBA do you know? I don't want to go into specific detail and give
you WAY too much information than you need.
Instead, I'll describe the process. Let me know if you need help with the
individual steps.

The next step is to decide how you want the blank rows to be "hidden".
Simply hiding the row would be the easiest. For this approach:
First, record a macro (call it "Hide_Rows") that hides a row, then unhides all
rows.
This will create the coding you'll need to accomplish your task.

Next, rearrange the macro you recorded so that it first unhides all rows.
Then, set up a loop from the first row of the summary  (excluding any header)
to the last row of the data (be sure to add a few rows for later "expansion").
In the loop, test the "Amount" value in each row to see if it is = "" or = 0,
if true, then modify the code recorded that hides a row to hide this row.

Test the sub.

Once you've got the sub working, In the VBA editor, double-click on the
"Summary"
sheet and you'll be taken to the code space for that sheet.
At the top left of the sheet panel, use the pull-down to select "Worksheet",
then on the right, use the pull-down to select "Activate".
This will create a Worksheet_Activate "event" subroutine.
In this event sub, enter the name of your new sub (Hide_Rows).

Now, go back to excel  and select the "Data" tab, then select the "Summary" tab.
The blank and 0 value rows should now be hidden.

If you need help with any or all of this, don't hesitate to ask!

 
Answer #4    Answered By: Chaths Massri     Answered On: Nov 13

You believe :Lines where the VALUE in the "Amount" column is blank or 0
should be hidden. (Right)
You believe :Data is changed in the "Data" tab. When the "Summary" sheet
is selected,
the data is automatically updated, and the blank or 0
value lines will be hidden.(Right)
=====================================================
Further, yes I already have "Summary" sheet  working to summarize the data.
First, I can record macro but can not understand VBA language so, I need
individual steps.
Secondly, I am not familiar with "loop" function.
So I couldn't go to next steps you advised me.
(In the end I expect VBA code to extract the summary  sheet)

 
Answer #5    Answered By: Tarron Thompson     Answered On: Nov 13

"In the end I expect VBA code to extract the summary  sheet"
does not compute... "extract" it? To where?
Extract:
a: to draw forth <extract data>
b: to pull or take out forcibly
c: to obtain by much effort from someone unwilling

OK... as in "extract data".. isn't that what the Summary sheet  already does?
It's extracting the data from the "Data" sheet.
Or are you really saying you want to "Extract" the summary sheet into a separate
workbook?

I think what you're wanting to do is "format" the Summary sheet.

is there a "fixed" number of rows? or will it change from one day to another?
I'll have to make some assumptions.
Assumption #1: in the Summary sheet,
The the Header row is row 1
Column A is for "Sr", Column B is "Expense Type", Column C is "Amount".
for each row with data, Column A has a value.

Then, if you follow my previous instructions,
In the VBA Module, put:

Option Explicit
Dim RowCnt, RowNum
Sub Hide_Rows()
Rows("1:65500").EntireRow.Hidden = False
RowCnt = Application.WorksheetFunction.CountA(Range("A1:A65500"))
For RowNum = 2 To RowCnt
If ((Cells(RowNum, 3) = 0) _
Or (Cells(RowNum, 3) = "")) Then
Cells(RowNum, 1).Rows.EntireRow.Hidden = True
End If
Next RowNum
End Sub

in the Sheet module for the "Summary" sheet, put:

Private Sub Worksheet_Activate()
Hide_Rows
End Sub

If my initial assumptions are wrong, then this macro will have to be modified.

 
Answer #6    Answered By: Vid Fischer     Answered On: Nov 13

I tried your code but it only hide  rows in "summary" sheet  but does
not pull summary  data from "Data" sheet.
Here Summary sheet already exist & extracting the data from "Data Sheet"
and there are not "fixed" no of rows.
Your Assumption (The the Header row is row 1 &
Column A is for "Sr", Column B is "Expense Type", Column C is "Amount") is
OK upto here.

 
Didn't find what you were looking for? Find more on Auto Hide & Unhide summary sheet thru VBA Or get search suggestion and latest updates.




Tagged: