Solved

Delete Table row based on cell value

Posted on 2012-04-02
2
335 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now