Filtering and deleted rows

  • Thread starter Thread starter ANDREW MC
  • Start date Start date
A

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?
 
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
 
Ok, that worked but now it selects eveything after deleting the unwanted
rows. Anywhere you click it'll select everything.
 
Andrew,

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

HTH,
Bernie
MS Excel MVP
 
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?
 
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
 
Thank you, works like a charm.
Bernie Deitrick said:
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
Click to expand...
 
Back
Top