Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
alert if selection contains hidden rows
I have a filtered sheet.
Sometimes I would want to autofill data in this sheet, but doing so would overwrite the hidden rows with filled data as well. I've accidentaly done this twice now, and don't want to make it a third. What I need is to check if the autofill range contains hidden rows and then prevent the autofill, or some other clever way to prevent the hidden data being overwritten. I've thought to use a worksheet_change event and then check if the range contains hidden rows, but this seems very inefficient. This is Excel97, btw. -Atle |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
alert if selection contains hidden rows
Well unless you want to control the autofill with a macro (select first cell
then an inputbox or userform will ask for the autofill range and settings), I do not see any event that could relate to this function. Even the worksheet_change event happens too late. The only way I see is to use the worksheet_selectionchange event and to select the range before you use the autofill: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim a As Range, message As String message = "The range you have selected contains hidden cells!" If IsArray(Target) Then For Each a In Target If a.EntireRow.Hidden = True Then MsgBox message, vbExclamation, "" Exit Sub End If Next End If End Sub <<<<<<<<<<<<<<< "atledreier" wrote: I have a filtered sheet. Sometimes I would want to autofill data in this sheet, but doing so would overwrite the hidden rows with filled data as well. I've accidentaly done this twice now, and don't want to make it a third. What I need is to check if the autofill range contains hidden rows and then prevent the autofill, or some other clever way to prevent the hidden data being overwritten. I've thought to use a worksheet_change event and then check if the range contains hidden rows, but this seems very inefficient. This is Excel97, btw. -Atle |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
alert if selection contains hidden rows
Surely this is all too complicated!
- Only use auto fill if you know you have no hidden rows - just be aware that before you double click on that + you must check! - Have the 'select visible cells tool 'in your custom toolbar and use it to copy something just to visible cells when you have a filter "atledreier" wrote: I have a filtered sheet. Sometimes I would want to autofill data in this sheet, but doing so would overwrite the hidden rows with filled data as well. I've accidentaly done this twice now, and don't want to make it a third. What I need is to check if the autofill range contains hidden rows and then prevent the autofill, or some other clever way to prevent the hidden data being overwritten. I've thought to use a worksheet_change event and then check if the range contains hidden rows, but this seems very inefficient. This is Excel97, btw. -Atle |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
alert if selection contains hidden rows
Dom, my users are goats and can't be trusted to check this.
Dude, that code worked as intended, thanks. Now, if I could further modify it to prevent a few 'false' pop-ups. When the users work on filtered data they often select across hidden rows, which isn't a problem until they use autofill. Maybe some code to detect an autofill, so that they can at least use undo right away if they messed up... Any tips on that? On Nov 23, 12:26 pm, DomThePom wrote: Surely this is all too complicated! - Only use auto fill if you know you have no hidden rows - just be aware that before you double click on that + you must check! - Have the 'select visible cells tool 'in your custom toolbar and use it to copy something just to visible cells when you have a filter "atledreier" wrote: I have a filtered sheet. Sometimes I would want to autofill data in this sheet, but doing so would overwrite the hidden rows with filled data as well. I've accidentaly done this twice now, and don't want to make it a third. What I need is to check if the autofill range contains hidden rows and then prevent the autofill, or some other clever way to prevent the hidden data being overwritten. I've thought to use a worksheet_change event and then check if the range contains hidden rows, but this seems very inefficient. This is Excel97, btw. -Atle- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening a group but keep hidden rows hidden | Excel Discussion (Misc queries) | |||
Hidden rows columns won't stay hidden | Excel Worksheet Functions | |||
Rows hidden by Autofilter vs hidden by changing the Hidden property | Excel Programming | |||
I need my Hidden Rows to stay hidden when I print the sheet. | Excel Discussion (Misc queries) | |||
Cell selection with hidden rows | Excel Programming |