Solved

Securing that autofilter does not show the last row, which is hidden, when show all is selected

Posted on 2013-10-30
10
84 Views
Last Modified: 2014-10-22
I have a sheet, that is send to coleagues, which in the input sheet has the autofilter applied.

The last line in the input sheet is hidden, and they will need to hit an insert line button to get new lines in the sheet.

Insert function is deactivated, and the last line is hidden, to secure that all calculation rules will be followed in new input lines.

But when I first activate the filter with some criteria, and then sets the autofilter to show all, my hidden line is shown.

for instance if my input data is in line a1to P10, I want the autofilter range to select this area. And even if line 11 (which is hidden) is using the same datavalidation rules as the 10 above, I do not wish to include that in my autofilter.

And if I insert an extra line the autofilter should now be applied to a1 to P11, and line 12, which is now hidden should still not be included in my autofilter.

Can that be done in Excel without VBA? Can I do it with VBA, if that is not a possibility?

regards

Jørgen
0
Comment
Question by:Jorgen
  • 5
  • 3
10 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39611340
Hi,

If you add a volatile function into any cell on the same worksheet, for example, a single cell with the formula: =NOW(), then when an AutoFilter selection is made a Worksheet_Change() event will be fired.

Within the Worksheet_Change() code for the worksheet you simply set the RowHeight of Row 11 to 0.

For example:

Private Sub Worksheet_Calculate()

 If (ActiveSheet.AutoFilterMode) Then
    Application.EnableEvents = False
    Rows(11).RowHeight = 0#
    Application.EnableEvents = True
 End If
 
End Sub

Open in new window



I would suggest using a cell on Row 11 to include the =NOW() value.  Maybe even setting the foreground colo(u)r & the background (interior) colo(u)r to be the same so that this cell value is not visible.

However, if you already have at least one volatile cell in the same worksheet, the inclusion of a separate cell for this purpose will not be needed.

Of course, this approach assumes Automatic Calculation is enabled at the time the AutoFilter is being used.

BFN,

fp.

PS. I have attached a workbook that includes the above code.  Note that row 11 remains hidden when an individual selection is made in column [ B ], & also when all items in column [ B ] are shown.  Cell [C11] contains the formula: [ =NOW() ].
Q-28280930.xls
0
 
LVL 4

Author Comment

by:Jorgen
ID: 39611559
Hi fp

It works like a charm in your solution, and I even changed to

    Rows(11).Select
      With Selection
        .EntireRow.Hidden = True
      End With
 as this will be the code that I have in my sheet and which works with the insert new record part as well.

I will test in my own application, and if it works as smoothly as in your sheet I will accept your solution as correct. I will get back to you ASAP

Thanks
Jørgen
0
 
LVL 4

Author Comment

by:Jorgen
ID: 39611672
Hi fp,

it actually works to well in my workbook. When I insert a copy of my hidden line above the hidden line - it hides both the hidden line and the line that I actually just inserted.

Any suggestions for that?

regards

Jørgen
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39611787
If I understand your issue correctly, you changed my suggested code that explicitly set the height of the required row (11) so that it now sets the row height of the current row selected.

Now you are wondering why a manually inserted (&, hence, selected) row is being hidden.

Shall I let you read back what I just typed again so you can determine how to progress, or would you like to add my code to your workbook this time? ;)
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Author Comment

by:Jorgen
ID: 39611922
Hi fp
fair point, I will go for your code, and will even modify my existing code to give it a try

regards
Jørgen
0
 
LVL 4

Author Comment

by:Jorgen
ID: 39613687
Hi fp,

I tried to change to your solution, and it works like you said it will as long as it will always be row 11 that I want to hide.


The issue is that I have a button with some alternative code that inserts a line above the hidden row. This is a copy of the hidden row, and should be visible after insertion.

But no matter if I use your code, or my alternative version It makes both lines visible after filtering if I try to vary the row number or even related the rownumber to a named range.

regards
Jørgen
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39614100
Hi Jørgen,

I tried to change to your solution, and it works like you said it will as long as it will always be row 11 that I want to hide.

I think you'll find that is because that requirement was the (original) question.

The issue is that I have a button with some alternative code that inserts a line above the hidden row. This is a copy of the hidden row, and should be visible after insertion.

But no matter if I use your code, or my alternative version It makes both lines visible after filtering if I try to vary the row number or even related the rownumber to a named range.

I think I am going to need to see your workbook & code to appreciate the issue completely.

However, adding a named range to the "hidden" row, then specifically setting the Visible property of the row associated with the named range to False within the Worksheet_Calculate() event may be the eventual solution.
0
 
LVL 4

Author Comment

by:Jorgen
ID: 39614326
Hi FP,

I have stripped the file for most of the data. The idea is to push the insert button, which while the screen is locked
1. unhides the last and hidden row
2. copies the previous hidden row
3. inserts a copied row
4. hides the last row

This row is a named range. It is all made in VBA in the general module and inserted by the insert module.

It is essential for me, that there is always a line with the correct calculations and validations ready for being copied.

For the user it is essential, that they can have an overview of their changes, and not everybody elses changes. Therefore the autofilter.

While testing a number of users have used the autofilter, and then gone down to the last line, which is my (now visible) line for copying. As they can see an empty line, they key into that line, and when they afterwards try to copy, the hidden line is gone.

Therefore I need to secure, that they never see this line, and that it is below the autofilter.

Hope my explanation helps you

I have tried to play around with your code and the alternative I tried yesterday. I also tried to change the calculation method, so I would not have any calculations before my insert module did finish copying the line. But no luck. Feel free to make any change in both the insert part and on the worksheet calculation part

regards

Jørgen
Ressource-Change-to-Development-.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40396494
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now