When you start this macro and there is nothing to do it ruins the sheet

[Romano_odK]

Good morning,

I got this code for erasing hidden lines with works great, but when you do not have hidden lines and still start this macro it sort of ruins your sheet by adding a column and that is not what I want. Is there a way to prevent this from happening like and message for example?

Thank you for your time.

Application.EnableEvents = False
  Application.ScreenUpdating = False
  With ActiveSheet.ListObjects(1)
    .ListColumns.Add Position:=2
    On Error Resume Next
    .ListColumns(2).DataBodyRange.SpecialCells(xlVisible).Value = 1
    On Error GoTo 0
    If .Parent.FilterMode Then .Parent.ShowAllData
    .Sort.SortFields.Clear
    .Sort.SortFields.Add2 Key:=.ListColumns(2).DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending
    .Sort.Apply
    On Error Resume Next
    .ListColumns(2).DataBodyRange.SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
    .ListColumns(2).Delete
  End With
  Application.EnableEvents = False

 

[Peter_SSs]

Try adding these red lines

Application.EnableEvents = False
  Application.ScreenUpdating = False
  With ActiveSheet.ListObjects(1)
    If .Range.Columns(1).SpecialCells(xlVisible).Count < .Range.Rows.Count Then
      .ListColumns.Add Position:=2
      On Error Resume Next
      .ListColumns(2).DataBodyRange.SpecialCells(xlVisible).Value = 1
      On Error GoTo 0
      If .Parent.FilterMode Then .Parent.ShowAllData
      .Sort.SortFields.Clear
      .Sort.SortFields.Add2 Key:=.ListColumns(2).DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending
      .Sort.Apply
      On Error Resume Next
      .ListColumns(2).DataBodyRange.SpecialCells(xlBlanks).EntireRow.Delete
      On Error GoTo 0
      .ListColumns(2).Delete
    End If
  End With
  Application.EnableEvents = False

.. but shouldn't that last line be

Application.EnableEvents = True

 

[Romano_odK]

Thank you that solved the problem and yes you are right about the EnableEvents that should indeed be TRUE.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.