Results 1 to 3 of 3

Trying to create a macro to delete entire rows based on cell value that is variable in length and characters

This is a discussion on Trying to create a macro to delete entire rows based on cell value that is variable in length and characters within the Excel Questions forums, part of the Question Forums category; Hello, I am trying to create a macro to use on a speadsheet that checks the value of a cell ...

  1. #1
    New Member
    Join Date
    Sep 2008
    Location
    Pennsylvania
    Posts
    20

    Default Trying to create a macro to delete entire rows based on cell value that is variable in length and characters

    Hello,

    I am trying to create a macro to use on a speadsheet that checks the value of a cell in Column A and if it meets a criteria (which is variable) then it will delete the entire row.

    The contents of the cells is below. The "Pc. Price:" part does not change, however the price does. The price can be from a whole number with no decimal point to having thousandths of a cent (i.e. .003).

    The Pc. Price, Total Value, Total Pieces, Total Cartons, and Delivery address are the only things I want to be deleted. I can not figure out how to delete a line based on part of the cell value. The information is electronically fed to me so I have no choice in how it shows up on the excel worksheet. (I tried copy and paste into excel to hopefully pull the information apart, however last time I tried that it all went into column A anyways.)

    Each row of information below is in 1 cell together, the decription of the value, and the value.

    Pc. Price: 0.03
    Total Line Value: 300
    Line I want to Keep
    Line I want to Keep
    Line I want to Keep
    Total Pieces: 10000
    Total Cartons: 100
    Line I want to Keep
    Line I want to Keep
    Delivery to: adress with letters, numbers, and dash's possible
    Line I want to Keep


    I've recorded a macro, however when I go to delete the lines it deletes the same row numbers everytime. I go through about 20 of these sheets a day, and the data for each part can be from 17-19 rows long, and there can be up to 200ish groups of the 17-19 lines of data on each sheet. So that didn't work out to well for me . Any help would be appreciated. Thanks!

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,134

    Default Re: Trying to create a macro to delete entire rows based on cell value that is variable in length and characters

    Welcome to the Board!

    See if this gets you started:

    Sub foo()
        Dim i As Long
            
            For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
                If InStr(Cells(i, "A").Text, "Pc.") Or InStr(Cells(i, "A").Text, "Total") Or InStr(Cells(i, "A").Text, "Delivery") Then
                    Cells(i, "A").EntireRow.Delete
                End If
            Next i
                
    End Sub


    Hope that helps,
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  3. #3
    New Member
    Join Date
    Sep 2008
    Location
    Pennsylvania
    Posts
    20

    Default Re: Trying to create a macro to delete entire rows based on cell value that is variable in length and characters

    Quote Originally Posted by Smitty View Post
    Welcome to the Board!

    See if this gets you started:

    Sub foo()
    Dim i As Long

    For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    If InStr(Cells(i, "A").Text, "Pc.") Or InStr(Cells(i, "A").Text, "Total") Or InStr(Cells(i, "A").Text, "Delivery") Then
    Cells(i, "A").EntireRow.Delete
    End If
    Next i

    End Sub


    Hope that helps,


    Thanks! I got the macro to delete the lines I was looking for (there were about 10 more than that). I added my original code for the headings and the inserts and formatting and everything works great!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com