Filtering and deleted rows

[ANDREW MC]

Is there a way to prevent the deletion of rows that fall between the rows
that are showing when filtering for certain cases?

 

[Bernie Deitrick]

Andrew,

You could prevent them from being selected: use this event: copy the coe,
right click on the worksheet tab, select "View Code" and paste the code in
the window that appears.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
Target.SpecialCells(xlCellTypeVisible).Select
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP

 

[ANDREW MC]

Ok, that worked but now it selects eveything after deleting the unwanted
rows. Anywhere you click it'll select everything.

 

[Bernie Deitrick]

Andrew,

It selects the visible cells in whatever range you've selected, no more, and
no less.

HTH,
Bernie
MS Excel MVP

 

[ANDREW MC]

Yes but I am unable to type if I would like to in a single cell. No matter
what you do it selects everything. Is there a way to retain the regular
spreadsheet functionality when filtering? Is there a setting in options that
may differ from spreadsheet to spreadsheet?

 

[Bernie Deitrick]

Andrew,

It works fine for me in XL XP on Windows XP SP2.

But given your described symptoms, try changing it to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Target.Cells.Count > 1 Then _
Target.SpecialCells(xlCellTypeVisible).Select
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP

 

[ANDREW MC]

Thank you, works like a charm.

Andrew,

It works fine for me in XL XP on Windows XP SP2.

But given your described symptoms, try changing it to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Target.Cells.Count > 1 Then _
Target.SpecialCells(xlCellTypeVisible).Select
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP