Delete Excel Rows Based On Cell Values

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.
LVL 1
G ScottAsked:
Who is Participating?
 
G ScottConnect With a Mentor Author Commented:
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
 
jsdrayCommented:
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
 
G ScottAuthor Commented:
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
 
G ScottAuthor Commented:
I figured this out on my own.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.