Solved

Delete Excel Rows Based On Cell Values

Posted on 2013-05-22
4
418 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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 …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
A short film showing how OnPage and Connectwise integration works.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

947 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

22 Experts available now in Live!

Get 1:1 Help Now