+ Reply to Thread
Results 1 to 4 of 4

Delete Row if Cell Contains Value

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Delete Row if Cell Contains Value

    I'm trying to search column A from the bottom up to delete an entire row if the cell contains the "Customer/" header info.

    However my code below keeps deleting all rows?

    'Delete Page "Headers" within Body
    Range("A:A").Select
    Firstrow = ActiveSheet.UsedRange.Cells(1).Row
    Lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
    'Loop through used cells backwards and delete if needed
    For lrow = Lastrow To Firstrow Step -1
        Set workrange = Cells(lrow, 4)
        If workrange.Value = "Customer/" & Chr(10) & "Invoice Number" Then
            workrange.EntireRow.Delete
        End If
    Next lrow
    Ideally, I will need this once working to delete both that row where "Customer/" is found as well the 2 rows above and 2 rows below, which I think I can do with an offset as well?

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delete Row if Cell Contains Value

    Maybe:

    Sub ptmuldoony()
    Firstrow = ActiveSheet.UsedRange.Cells(1).row
    LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).row
    
    'Loop through used cells backwards and delete if needed
    For lRow = LastRow To Firstrow+2 Step -1
        If Cells(lRow, 4) Like "*Customer/*" And Cells(lRow, 4) Like "*" & Chr(10) & "*" And Cells(lRow, 4) Like "*Invoice Number*" Then
            Rows(lRow - 2).Resize(4).Delete
        End If
    Next lRow
    End Sub
    Last edited by JOHN H. DAVIS; 02-22-2017 at 02:12 PM.

  3. #3
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Re: Delete Row if Cell Contains Value

    Thanks John

    But unfortunately that code didn't seem to delete the rows. I know its possibly to try and filter the data for the "Customer/" header row and delete those visible cells/rows. But do you know if possible after the filter to also delete the 2 rows above and 2 rows below that header row using an Offset as well?

    I'm gonna do some google searches and work some more on it today as well.

    Thanks
    Last edited by ptmuldoon; 02-23-2017 at 11:10 AM.

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delete Row if Cell Contains Value

    Can you attach a sample file. To me it seems would be ackward working on filtered cells. I think the best way would be using a loop somehow from bottom to top. But if the code I suggested doesn't work, I think attaching a file with some sample data to test may be helpful in finding a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA code to delete the FILTERED range and moving the cell up (dont Delete entire row)
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-14-2015, 07:48 AM
  2. [SOLVED] Delete cell only with certain critria. Don't want to delete entire row, the celsl only
    By RobertOHare in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-16-2013, 04:18 AM
  3. Run Macro Automatically When delete row , changes cell,delete column
    By elevisse in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2012, 07:42 AM
  4. Cell to Tab Macro Delete Tab when Cell Delete
    By Wskip49 in forum Excel General
    Replies: 4
    Last Post: 03-16-2011, 02:37 PM
  5. Delete Rows by Cell Value - Delete sheets w/out prompt
    By Jimmydageek in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2010, 02:03 PM
  6. Delete entire row when cell says delete
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-27-2007, 11:46 AM
  7. Replies: 0
    Last Post: 05-24-2005, 05:42 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1