alert if selection contains hidden rows

[atledreier]

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

 

[The Dude]

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

 

[DomThePom]

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]

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?