vba to resize table - removing blank cells

[Steven101]

Hi I've come across the following code which checks for any blank data in a table column and deletes the blank rows. It works great when there are blank rows or where there are multiple rows of data and no blank rows.

It errors out when there is only 1 row of data and no blank rows....I get the following error..."Run-time errir '1004': This won't work because it would move cells in a table on your worksheet.

Sub ResizeTable3()
  Dim Rng As Range
  On Error Resume Next
  Set Rng = Range("Table3[[Staff]]").SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0
  If Not Rng Is Nothing Then
    Rng.Delete Shift:=xlUp
  End If

Any help would be appreciated.

Thanks, Steven

 

[gallen]

Not tested but you could just check that you have more than 1 row...

  If Not Rng Is Nothing Then
      [B][COLOR=#ff0000]If Rng.Rows.Count > 1[/COLOR][/B] Then Rng.Delete Shift:=xlUp
  End If

 

[Steven101]

Thanks for the quick response, unfortuneltely that doesn't work. Strangely when only 1 row of data the Rng.Rows.Count = 3, so tries to delete rows.

Not tested but you could just check that you have more than 1 row...

  If Not Rng Is Nothing Then
      [B][COLOR=#ff0000]If Rng.Rows.Count > 1[/COLOR][/B] Then Rng.Delete Shift:=xlUp
  End If

 

[gallen]

would If Rng.Rows.Count > 3 work?