Solved

VBA to Delete Table rows based on value in first column

Posted on 2014-09-24
18
3,767 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
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 25

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
 
LVL 25

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 25

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 25

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 31

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 25

Expert Comment

by:ProfessorJimJam
ID: 40341862
yes you can
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

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 25

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 25

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 31

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 31

Accepted Solution

by:
Rob Henson earned 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

762 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

20 Experts available now in Live!

Get 1:1 Help Now