Hidden rows reappearing

[Taylor Francis]

I have sheet the uses about 10 columns and 20 rows. For convenience and
appearance, I hid all the unused cols and rows. I often insert new rows
and delete old rows... (it's a checkbook register type spreadsheet).
When I delete a row, one of the hidden rows at the bottom reappears...
and it's not row 21 but rather something in the 2000s or 3000s. Why
does this happen and is it preventable?

Thanks,
Taylor

 

[Héctor Miguel]

hi, Taylor !

I have sheet the uses about 10 columns and 20 rows.
For convenience and appearance, I hid all the unused cols and rows.
I often insert new rows and delete old rows... (it's a checkbook register type spreadsheet).
When I delete a row, one of the hidden rows at the bottom reappears...
and it's not row 21 but rather something in the 2000s or 3000s.
Why does this happen and is it preventable?

AFAIK, when you apply formats [like hide/show] to rows/columns, excel re/defines the 'usedrange'
so [and i'm guessing]... deleting rows/columns 'coerces' excel to show -previously- 'hidden' rows/columns -?-
[as far as they are at the bottom of new/previous 'usedrange'] -?-

i played with the following procedure [trying not to loose the stack for undo levels] and its working 'fine'...

1) go to that cell you need/want/... to stay as your 'last visible' cell/row/column and give it a name [i.e. myLastRow]

2) copy/paste the following lines into appropriate sheet code-module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Tmp As String
Application.EnableEvents = False
With Range(Range("mylastrow").Offset(1), _
Cells(Rows.Count, Range("mylastrow").Column))
On Error Resume Next
Tmp = .SpecialCells(xlCellTypeVisible).Address
On Error GoTo 0
End With
If Tmp <> "" Then Range(Tmp).EntireRow.Hidden = True
Application.EnableEvents = True
End Sub

hth,
hector.