2

I try to run the following script in order to delete empty rows in an excel table. I get the error: "Cannot use command on overlapping selections " What can be the reason ? 

Dim Rng2 As Range
On Error Resume Next
Set Rng2 = Range("Table2").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not Rng2 Is Nothing Then
     Rng2.Delete Shift:=xlUp
End If

1 Answers1

1

Try the following.

Note: Change > 0 to > 1 if need blanks if 2 columns etc

Code:

Option Explicit

Public Sub test()
    Dim Rng2 As Range
    With ActiveSheet
        For Each Rng2 In .Range("Table2").Rows
            If Application.WorksheetFunction.CountBlank(Rng2) > 0 Then Rng2.Delete
        Next Rng2
    End With
End Sub

With filtered tables you may need need to unfilter and then delete

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • Hi, It doesn't work. "This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet" –  Jun 03 '18 at 09:33
  • Wow... that worked for me. Do you have other tables on the sheet? – QHarr Jun 03 '18 at 09:33
  • Yes I have one more table (Table1) and the script: that I gave do work for it. –  Jun 03 '18 at 09:35
  • And does it overlap with table2? Though, tbh, it seems to work fine for me still. – QHarr Jun 03 '18 at 09:35
  • No, they are on the same rows but not on the same columns. maybe this is the problem? –  Jun 03 '18 at 09:37
  • To be honest, at present I cannot duplicate your problem as works fine for me. Can we see your table layouts? Don't show any sensitive data. – QHarr Jun 03 '18 at 09:39
  • First of all thanks for your help. I have added my excel layout screen shot. –  Jun 03 '18 at 09:43
  • Have a look at new answer. This works on any blank in row. Change > 0 to > 1 if need blanks if 2 columns etc. – QHarr Jun 03 '18 at 09:48
  • Is your table filtered in some way? – QHarr Jun 03 '18 at 09:59
  • Or filter table with .AutoFilter Field:=1, Criteria1:="=" and then delete the visible cells minus header. https://stackoverflow.com/questions/22541846/delete-blank-rows-after-autofilter?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – QHarr Jun 03 '18 at 10:09
  • Hi, I don't know why it is, but the "Public Sub test()" solution deletes only half of the empty lines. besides that I get no errors. Thanks for your answers. I will try to figure that out. –  Jun 03 '18 at 11:30
  • If you do For Each Rng2 In .Range("Table2").Rows : Debug.print Rng2.Address and look in the immediate window (Ctrl + G) - do all the table rows with blanks in appear listed? – QHarr Jun 03 '18 at 12:02
  • Failing that do you have a file without sensitive info in to share? – QHarr Jun 03 '18 at 12:03