Solved

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

Posted on 2013-10-30
10
121 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 47

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

726 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