Well, how hidden is it ??

[Gary''s Student]

I have noticed that if I hide a row and then set a range across that hidden
row, the value gets applied to the “hidden cellâ€:

Sub sub1()
Range("A2").EntireRow.Hidden = True
Range("A1:A3").Value = 1
Range("A2").EntireRow.Hidden = False
End Sub

The value gets applied to A2, even though the cell was hidden. If, however,
I hide the row using AutoFilter, the value in A2 does not get changed. It is
possible to apply this type of “hiding†without using AutoFilter??

 

[Simon]

I have noticed that if I hide a row and then set a range across that hidden
row, the value gets applied to the “hidden cell”:

Sub sub1()
Range("A2").EntireRow.Hidden = True
Range("A1:A3").Value = 1
Range("A2").EntireRow.Hidden = False
End Sub

The value gets applied to A2, even though the cell was hidden.  If, however,
I hide the row using AutoFilter, the value in A2 does not get changed.  It is
possible to apply this type of “hiding” without using AutoFilter??

Good question, i don't have an answer, but I suggest doing it via
autofilter then (in code) turn the autofilter off. Maybe that would
work?

 

[Rick Rothstein]

Try it this way...

Sub sub1()
Range("A2").EntireRow.Hidden = True
Range("A1:A3").SpecialCells(xlCellTypeVisible).Value = 1
Range("A2").EntireRow.Hidden = False
End Sub

 

[Gary''s Student]

Thanks Rick, good suggestion.

 

[Rick Rothstein]

You are welcome. By the way, don't forget to put in some error trapping in
your general code just in case all the cells are hidden.