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

x
?
Solved

Delete Excel Rows Based On Cell Values

Posted on 2013-05-22
4
Medium Priority
?
467 Views
Last Modified: 2013-05-28
So I am trying to implement an Excel macro I found into a VB.NET project.  All it does is looks for values in a column in Excel and deletes the entire row if found.  The macro is this:

    Sub DeleteRowWithContents()
        '========================================================================
        ' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Record Only" IN COLUMN D
        '========================================================================
        Last = Cells(Rows.Count, "D").End(xlUp).Row
        For i = Last To 1 Step -1
            If (Cells(i, "B").Value) = "MLD24" Then
                'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
                Cells(i, "A").EntireRow.Delete()
            End If
        Next i
    End Sub

Open in new window


I can loop through all the rows and get the values in the cells of column B, but I can't figure out how to delete the row if found.

Public Sub deleterowsfromexcel()
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open("C:\Temp2\pass.csv")
        xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)

        'MessageBox.Show(xlWorkSheet.Name)
        Dim xlCells As Excel.Range = Nothing
        Dim therows As Integer = xlWorkSheet.UsedRange.Rows.Count()
        MsgBox(therows.ToString)

        Dim SingleCellToRead = xlWorkSheet.Range("B2:B" & xlWorkSheet.UsedRange.Rows.Count())
        For Each row In SingleCellToRead.Rows.Value

            MsgBox(row.ToString)


        Next
        xlApp = Nothing
        xlWorkBooks = Nothing
        xlWorkBook = Nothing
        xlWorkSheet = Nothing
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

    End Sub

Open in new window


I want to be able to pass the value that I want deleted by doing something like this:

Public Sub deleterowsfromexcel(ByVal stringtodelete as string)

Thanks for any help you can give me on this.
0
Comment
Question by:G Scott
[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
  • 3
4 Comments
 
LVL 9

Expert Comment

by:jsdray
ID: 39189019
Sub Delete_Rows()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A1:C20"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "Apple" _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:G Scott
ID: 39190418
jsdray - this is what I now have and it doesn't delete the rows:

 Public Sub deleterowsfromexcel(ByVal thestring As String)
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open("C:\Temp2\pass.csv")
        xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)

        'MessageBox.Show(xlWorkSheet.Name)
        Dim xlCells As Excel.Range = Nothing
        Dim therows As Integer = xlWorkSheet.UsedRange.Rows.Count()
        MsgBox(therows.ToString)

        Dim SingleCellToRead = xlWorkSheet.Range("B2:B" & xlWorkSheet.UsedRange.Rows.Count())
        For Each row In SingleCellToRead.Rows.Value

            'MsgBox(row.ToString)
            With xlApp

                Dim rng As Excel.Range, cell As Excel.Range, del As Excel.Range
                rng = .Intersect(.Range("B2:B" & xlWorkSheet.UsedRange.Rows.Count()), .ActiveSheet.UsedRange)
                For Each cell In rng
                    MsgBox(cell.Value.ToString)
                    If (cell.Value) = "ASM01" Then
                        If del Is Nothing Then
                            del = cell
                        Else : del = .Union(del, cell)
                        End If
                    End If
                Next cell
                On Error Resume Next
                del.EntireRow.Delete()

            End With

        Next
        xlApp = Nothing
        xlWorkBooks = Nothing
        xlWorkBook = Nothing
        xlWorkSheet = Nothing
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

    End Sub

Open in new window

0
 
LVL 1

Accepted Solution

by:
G Scott earned 0 total points
ID: 39192154
I figured it out on my own.  Thanks for your help:
 Sub pleasework(ByVal thestring As String, ByVal theworkbook As String)
        Dim xlApp As Excel.Application = New Excel.Application
        Dim wbBook As Excel.Workbook
        Dim wbBooks As Excel.Workbooks
        Dim wsSheet As Excel.Worksheet
        Dim rnCheck As Excel.Range, rnFind As Excel.Range, rnDel As Excel.Range
        Dim stAddress As String
        wbBooks = xlApp.Workbooks
        wbBook = wbBooks.Open(theworkbook)
        wsSheet = wbBook.ActiveSheet
        With wsSheet
            'rnCheck = .Range("B1:B" & wsSheet.UsedRange.Rows.Count())
            Dim last As Integer = (wsSheet.UsedRange.Rows.Count())
            'MsgBox(last.ToString)
            For i = last To 1 Step -1
                If (.Cells(i, "B").value) = thestring Then
                    .Cells(i, "A").EntireRow.Delete()
                    'MsgBox(i.ToString)
                End If
            Next
        End With
        xlApp.DisplayAlerts = False
        wbBook.Save()
        wbBook.Close()
        wbBooks.Close()
        'xlApp = Nothing
        'wbBook = Nothing
        'wbBooks = Nothing
        xlApp.Quit()
    End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:G Scott
ID: 39200663
I figured this out on my own.
0

Featured Post

Independent Software Vendors: 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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

722 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