• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

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

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
Jorgen
Asked:
Jorgen
  • 5
  • 3
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
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
 
JorgenAuthor Commented:
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
 
JorgenAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
[ fanpages ]IT Services ConsultantCommented:
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
 
JorgenAuthor Commented:
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
 
JorgenAuthor Commented:
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
 
[ fanpages ]IT Services ConsultantCommented:
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
 
JorgenAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now