vba to resize table - removing blank cells

Steven101

Board Regular
Joined
Oct 27, 2014
Messages
57
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.



Code:
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
Not tested but you could just check that you have more than 1 row...

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

Steven101

Board Regular
Joined
Oct 27, 2014
Messages
57
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...

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

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,921
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top