Solved

Delete Excel Rows Based On Cell Values

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

13 Experts available now in Live!

Get 1:1 Help Now