Solved

Delete Excel Rows Based On Cell Values

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

777 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