Solved: Delete Unused rows in a table

[fredlo2010]

Hello,

I have this table and I want to delete all the table rows that are empty. The problem comes in when I am trying to delete the "Table Row" and not the excel entire row. There is data next to my table and I don't wanna mess it up.


This is the code I managed to put togeter

Sub Delete_Empty_Rows_Table()
Dim i As Variant
Dim varWorkbook As Workbook
For Each i In varWorkbook
Set i = ListObjects.ListRows.Count
If Range("B7:B10000").Value = "" Then
Selection.ListObject.ListRows(i + 1).Delete
End If
Next i
End Sub

[Opv]

Try this:


Sub Delete_Empty_Rows_Table()
Dim blankRng As Range
On Error Resume Next
Set blankRng = Range("B7:B10000").SpecialCells(xlCellTypeBlanks)
blankRng.Delete shift:=xlUp
On Error GoTo 0
End Sub

[fredlo2010]

Thanks for the help Ovp,

It worked the first time I used it then it does not work any more. I have tried several reasons and I don't know whats going on

[Opv]

Thanks for the help Ovp,

It worked the first time I used it then it does not work any more. I have tried several reasons and I don't know whats going on

Can you post an example of your workbook?

[Kenneth Hobs]

The SpecialCells method requires a fresh save to work properly each time.

[fredlo2010]

Can you post an example of your workbook?


here is the sample of the workbook

8147

Thanks

[Opv]

here is the sample of the workbook

8147

Thanks
Following Kenneth's note, try this amended version, which saves the workbook automatically:


Sub Delete_Empty_Rows_Table()
Dim blankRng As Range
On Error Resume Next
Set blankRng = Range("B7:B10000").SpecialCells(xlCellTypeBlanks)
blankRng.Delete shift:=xlUp
On Error GoTo 0
ThisWorkbook.Save
End Sub

[fredlo2010]

Ovp,

I think you forgot to put code in the wrap tags. I am not sure about the idea of the saving. As a matter of fact saving the working book with a specific name an in a specific directory is something I am working on right now. But i am not sure.

[Opv]

Ovp,

I think you forgot to put code in the wrap tags. I am not sure about the idea of the saving. As a matter of fact saving the working book with a specific name an in a specific directory is something I am working on right now. But i am not sure.

The code is there now. I had to edit it...pasted the wrong code in the first time. Sorry about that.

[fredlo2010]

Hello,

I got some updates on this and I wanted to share them with you guys.

Thanks to the help from jindon from this forum (http://www.ozgrid.com/forum/showthread.php?t=165842&p=610425&posted=1#post610425) I have this code now:

Sub Cleanup()

WaitCleanupForm.Show

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim i As Long
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
If Cells(i, 2).Value = "" Then
Select Case Cells(i, 1).Value
Case "Cookies", "Salt", "Pepper", ""
Case Else
Cells(i, 1).Resize(1).Delete xlShiftUp
End Select
End If
Next


'reset print area

If Range("A35") = "" Then

ActiveSheet.PageSetup.PrintArea = Range("A1:N34").Address
Else
ActiveSheet.PageSetup.PrintArea = Range("A1:N1", Range("A" & Rows.Count).End(xlUp)).Address
End If

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

WaitCleanupForm.Hide

End Sub


The code works fine the only issue is that it takes quite some time to run. I had to create a userform to inform that hold on this has not crashed on you this is just a slow macro.

Maybe you guys can suggest something to speed it up a little.

[fredlo2010]

Hi,

Ok nows its all done. I figured it out. First I forgot to set my
WaitCleanupForm ShowModal to false, so the form was pretty much there forever. I also added some code to set the calculations to manual and then back to automatic. ( this helped a lot)

so here is my code now

Sub Cleanup()

WaitCleanupForm.Show
WaitCleanupForm.Repaint

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Dim i As Long
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
If Cells(i, 2).Value = "" Then
Select Case Cells(i, 1).Value
Case "Cookies", "Salt", "Pepper", ""
Case Else
Cells(i, 1).Resize(1).Delete xlShiftUp
End Select
End If
Next


'reset print area


If Range("A35") = "" Then

ActiveSheet.PageSetup.PrintArea = Range("A1:N34").Address
Else
ActiveSheet.PageSetup.PrintArea = Range("A1:N1", Range("A" & Rows.Count).End(xlUp)).Address
End If

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

WaitCleanupForm.Hide

End Sub

Oh I also had to repaint the form because the screenupdating set to flase was making it show up blank

Thank you so much for the help
This thread has been solved

The original thread can be found here (http://www.ozgrid.com/forum/showthread.php?t=165842&p=610425&posted=1#post610425)