Macro to delete table row, not entire row

[smiley3141]

Hi there.

I am using Excel 2007 and I have a table called "Data_Table." It is in columns E through AA. The Column Heading in AA is "Validity." I would like help with a macro that can delete all table rows where the Validity is "Outdated". There is hidden data to the left of the table so I cannot delete the entire rows.

Thank you for your help.

 

[Firefly2012]

Hi

I think you'll need a looping routine for that as I don't *think* you can use Autofilter (which normally would be a very quick method).

Eg, try:

Sub Remove_Rows()
Dim i As Long

For i = Cells(Rows.Count,"AA").End(xlUp).Row to 2 Step -1   
  If Cells(i,"AA").Value = "Validity" Then _
      Range("E" & i,"AA" & i).Delete Shift:=-4162
Next i

End Sub

Note that in the above I have assumed your headers are in row 1

 

[smiley3141]

Thank you, Firefly2012. That does the trick.

 

[smiley3141]

Firefly2012,

If you don't mind, would you please explain to me what the "Shift:=-4162" part of the VBA does?

 

[mirabeau]

Hi there.

I am using Excel 2007 and I have a table called "Data_Table." It is in columns E through AA. The Column Heading in AA is "Validity." I would like help with a macro that can delete all table rows where the Validity is "Outdated". There is hidden data to the left of the table so I cannot delete the entire rows.

Thank you for your help.

If you're so inclined, you should be able to do it without looping.

Sub delrow()
With Columns("AA:AA")
    .Replace "", Chr(30)
    .Replace "Outdated", ""
    Intersect(.SpecialCells(4).EntireRow, Range("E:AA")).Delete 2
    Replace Chr(30), ""
End With
End Sub

Although nothing at all wrong with looping if done sensibly.

 

[smiley3141]

Mirabeau,

I cannot get that macro to work. It keeps giving me an error. I have even trid to put a period before the final "Replace".

Also, using the looping method descirbed by Firefly2012, the rows get deleted, but I have noticed that I lose some of the regular table functions. For example, my table style is "Medium 9" - it has alternating rows of dark and light blue. After deleting the rows, this pattern no longer holds. Instead of alternating every row, the rows remember the colors they had before and stay that color, so I end up with large ranges of the same colored rows. Also, I cannot get the table to accept a new color pattern. Is there some way to delete the table rows and allow the coloring aspects to remain the same?

Thank you for any help you can give me.

 

[smiley3141]

Sorry, Mirabeau.

I was wrong. I must have been in some bad loop or something. Your code is now working and it preserves the alternating banding of the tables that was not preserved with the loop method.

Thank you, both, for your help.

 

[pgc01]

Hi

Another option would be to use the DataTable vba syntax. For ex.:

Sub DataTableDeleteRows()
Dim j As Long
    
    With ActiveSheet.ListObjects("Data_Table.")
        For j = .ListRows.Count To 1 Step -1
            If .ListRows(j).Range(1, .ListColumns("Validity").Index) = "Outdated" Then _
                .ListRows(j).Delete
        Next j
    End With
End Sub

 

[pgc01]

or ...

Sub DataTableDeleteRows()
Dim j As Long
    
    With ActiveSheet.ListObjects("Data_Table.")
        For j = .ListRows.Count To 1 Step -1
            If .ListColumns("Validity").DataBodyRange(j) = "Outdated" Then _
                .ListRows(j).Delete
        Next j
    End With
End Sub

 

[smiley3141]

Thank you, everyone, for the help.

I have tested each of the macros and they all work. Of the four, I have found the intersection macro to be the fastest. When I tested it on a table of 5,000 lines, the intersection macro was at least two to three times faster than all the others. When the data was sorted by the "Validity" column first, the difference was even more pronounced.