Results 1 to 2 of 2
  1. #1
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021

    How to remove rows based on cell values?

    Hello all - I have the macro below where I recently added "
    Criteria1:="= "
    so that I can remove any rows that do not start with DCQ or that are blank. The macro runs and deletes most of the rows where the cell is empty but it does not delete all of them. Please note that I added the LEN function to the workbook and found that some cells have 0 characters while others have 50. Any idea what I need to add to remove all rows that are blank with the addition of those that do not start with DCQ?

    Sub Clean_Format()    Dim ws As Worksheet
        Dim rng As Range
        Dim lastRow As Long
        Set ws = ActiveWorkbook.Sheets("Sheet1")
        lastRow = ws.Range("M" & ws.Rows.Count).End(xlUp).Row
        Set rng = ws.Range("M1:M" & lastRow)
        With rng
            .AutoFilter Field:=1, Criteria1:="<>DCQ*", Criteria1:="= "
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        ws.AutoFilterMode = False
    End Sub
    * Side question - if I want to format column A as Text and column DF as Time, where can I add that to the macro?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    I tested your code and as far as I can tell, it deleted all targeted rows. If you want to provide worksheet for analysis, follow instructions at bottom of my post.

    Use macro recorder to get formatting code.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-04-2021, 09:00 AM
  2. Sum Values for Rows based on certain criteria
    By mkelly1082 in forum Access
    Replies: 3
    Last Post: 03-27-2019, 09:47 AM
  3. Clear Cell Range Based on Text in Another Cell
    By Oxygen Potassium in forum Access
    Replies: 3
    Last Post: 08-20-2017, 08:12 PM
  4. Replies: 1
    Last Post: 05-14-2015, 06:38 PM
  5. Replies: 2
    Last Post: 08-28-2011, 06:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums