Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delete Table row based on cell value

Posted on 2012-04-02
2
Medium Priority
?
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

604 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