Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA to Delete Table rows based on value in first column

Posted on 2014-09-24
18
Medium Priority
?
5,328 Views
Last Modified: 2014-09-24
I am looking for simple code to cycle through a table and delete entire table row based on the value in column 1 of the table.

Thanks in advance
0
Comment
Question by:acdecal
[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
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40341815
can you upload an example file?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40341828
For introw = 9999 To 1 Step -1
    If Cells(introw, 1) = 43 Then
        Rows(introw).Delete
    End If
Next

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40341829
That goes through 9999 rows, and deletes any rows in which the value of column A is 43.
0
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.

 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40341836
you can put this in sheet object code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim i As Long
Dim LR As Long

Application.ScreenUpdating = False

LR = Cells(Rows.Count, 1).End(xlUp).Row

For i = LR To 2 Step -1

    If Range("A" & i) = "Your Text" Then
        Range("A" & i).EntireRow.Delete Shift:=xlUp
    End If
    
Next i

Application.ScreenUpdating = True
   

End Sub

Open in new window

0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40341843
see the = "Your Text"
change it as per your need.  if it is a number then remove the " " double quote and just put the number
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40341854
if you do not want that the row is deleted everytime a condtion is made.

then you can do it through simple running macro . by putting the code into the module and then run it there.

Sub Macro
Dim i As Long
Dim LR As Long

Application.ScreenUpdating = False

LR = Cells(Rows.Count, 1).End(xlUp).Row

For i = LR To 2 Step -1

    If Range("A" & i) = "Your Text" Then
        Range("A" & i).EntireRow.Delete Shift:=xlUp
    End If
    
Next i

Application.ScreenUpdating = True

End Sub

Open in new window

0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40341856
How about just using the Filter option on the table?

Filter column 1 to show only the values that you want to delete, outside of the table select the rows that are left visible and Delete Rows using "Ctrl & -" or the menu option on the Home Tab, Cells Group. This has to be done outside of the table because doing it within the table can cause strange results.

Disable Filter and those values that you wanted to keep will still be there.

Thanks
Rob H
0
 

Author Comment

by:acdecal
ID: 40341859
I just want to delete the rows in the table not the entire sheet row.
Can't I define the range as the table, cycle through the rows and check the value in the first column?
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40341862
yes you can
0
 

Author Comment

by:acdecal
ID: 40341867
what is the code for this?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40341869
I assume there is other data alongside the table that you need to keep? If not then it doesn't matter that you are deleting the whole row.

Thanks
Rob H
0
 

Author Comment

by:acdecal
ID: 40341887
Correct.  I just thought there may be a simple reference to table cells that I could use rather than defining the range over again.
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40341907
here is the code you need

Sub DeleteRowsSecondFastest()
''''''''''''''''''''''''''
'Written by www.ozgrid.com
''''''''''''''''''''''''''
Dim rTable As Range
Dim rCol As Range, rCell As Range
Dim lCol As Long
Dim xlCalc As XlCalculation
Dim vCriteria

On Error Resume Next
   'Determine the table range
     With Selection
         If .Cells.Count > 1 Then
             Set rTable = Selection
         Else
             Set rTable = .CurrentRegion
             On Error GoTo 0
         End If
    End With
   
    'Determine if table range is valid

    If rTable Is Nothing Or rTable.Cells.Count = 1 Or WorksheetFunction.CountA(rTable) < 2 Then
        MsgBox "Could not determine you table range.", vbCritical, "Ozgrid.com"
        Exit Sub
    End If

    'Get the criteria in the form of text or number.

    vCriteria = Application.InputBox(Prompt:="Type in the criteria that matching rows should be deleted. " _
    & "If the criteria is in a cell, point to the cell with your mouse pointer", _
    Title:="CONDITIONAL ROW DELETION CRITERIA", Type:=1 + 2)

    'Go no further if they Cancel.

    If vCriteria = "False" Then Exit Sub

    'Get the relative column number where the criteria should be found

    lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _
    & "the criteria can be found.", Title:="CONDITIONAL ROW DELETION COLUMN NUMBER", Type:=1)

    'Cancelled
    If lCol = 0 Then Exit Sub
        'Set rCol to the column where criteria should be found
        Set rCol = rTable.Columns(lCol)
        'Set rCell to the first data cell in rCol
        Set rCell = rCol.Cells(2, 1)

    'Store current Calculation then switch to manual.
    'Turn off events and screen updating
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

   

  'Loop and delete as many times as vCriteria exists in rCol
   For lCol = 1 To WorksheetFunction.CountIf(rCol, vCriteria)
        Set rCell = rCol.Find(What:=vCriteria, After:=rCell, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(-1, 0)
            rCell.Offset(1, 0).EntireRow.Delete
   Next lCol

   
    With Application
        .Calculation = xlCalc
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   On Error GoTo 0
   
   

End Sub

Open in new window

0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40341922
click on any cell on the given table that you want to have the macro run.

then it will ask you that enter the criteria the condition based on which you want to trigger the deletion,

then it will ask you the column in which the value need to be looked in.  in your case if it is column A then type 1 and if it is column B type 2

then click ok and it will do the job
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40341931
You should be able to use

Selection.ListObject.ListRows(1).Delete

Thanks
Rob H
0
 

Author Comment

by:acdecal
ID: 40341965
rCell.Offset(1, 0).EntireRow.Delete

I don't want to delete the entire worksheet row just the table row
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 40341974
A shorter simpler routine:
Sub DeleteTableRow()
    YourValue = "08"
    Range("F1").Select
    Do Until ActiveCell.Value = ""
        If ActiveCell.Value = YourValue Then
        R = ActiveCell.Row - 1
        Selection.ListObject.ListRows(R).Delete
        Else
        ActiveCell.Offset(1, 0).Select
        End If
    Loop
End Sub

Open in new window


Change the Variable "YourValue" to what you want to delete
Change the initial selection       Range("F1").Select     to select the top of the column to be assessed.

Thanks
Rob H
0
 

Author Closing Comment

by:acdecal
ID: 40342167
Thanks, that did the trick
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

721 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