Solved

Delete Table row based on cell value

Posted on 2012-04-02
2
343 Views
Last Modified: 2012-04-02
Hi All

Well I tried a few solutions and only would up making more mistakes.

I need a function that will delete rows from a table I have on a worksheet.  Basically if a cell from B2 down to B-whatever has the word "Delete" or "Delete2" in it I would like the that whole row deleted.  Note I don't want the worksheet row deleted, only the row in the excel table.  The data goes from B over to S.
0
Comment
Question by:deskchains
2 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 250 total points
ID: 37798131
This one was a bit tricky and took a bit more time to develop than I originally thought.  It turns out you can't delete listRows from a list object if the data is filtered.  I assume you are working with a list object as you call it a "table".  If not, please advise and I'll recode, accordingly.

Here's your code.  Note line 17 specifies the name of your table:
Option Explicit

Sub delRowsInTable()
Dim wkb As Workbook
Dim wks As Worksheet
Dim r As Range
Dim lRow As Long
Dim loTable As ListObject
Dim rDelete As Range

    Application.ScreenUpdating = False
    
    Set wkb = ThisWorkbook
    Set wks = wkb.ActiveSheet
    
    If wks.AutoFilterMode Then wks.AutoFilterMode = False
    Set loTable = wks.ListObjects("Table1")
    loTable.Range.AutoFilter field:=1, Criteria1:= _
        "=Delete", Operator:=xlOr, Criteria2:="=Delete2"
    
    Set rDelete = loTable.Range.Offset(1, 0).Resize(loTable.Range.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    
    loTable.Range.AutoFilter
    
    rDelete.Delete shift:=xlUp

    loTable.Range.AutoFilter
    
    Application.ScreenUpdating = True
End Sub

Open in new window


See attached.

Cheers,

Dave
delTableRow-r1v1.xls
0
 

Author Closing Comment

by:deskchains
ID: 37799156
Viola...wow definitely not the direction I was headed, but it works great!

Thanks much for your help, great job!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question