+ Reply to Thread
Results 1 to 12 of 12

Hide Rows with a Value (2 values to hide) then delete visible rows

  1. #1
    Registered User
    Join Date
    05-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Hide Rows with a Value (2 values to hide) then delete visible rows

    Okay, I know the basics of what I want, here, but I can't get it to work. I know how to hide with 1 criteria per column, but not 2 different ones. Here's what DOESN'T work, to give you an idea of what I am working with

    Dim Lastrow As Long
    Application.ScreenUpdating = False
    Lastrow = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    With Range("A2:I" & Lastrow)
    Selection.AutoFilter
    .AutoFilter Field:=6, Criteria1:= _
    "=<>Available",
    Criteria2:="=<>Reshelving"
    End With
    Range("A2:A" & Lastrow).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True

    Also fighting with me is

    Dim Lastrow As Long
    Application.ScreenUpdating = False
    Lastrow = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    With Range("A2:I" & Lastrow)
    Selection.AutoFilter
    .AutoFilter Field:=6, Criteria1:= _
    "=<>Available", Operator:=xlOr, Criteria2:="=<>Reshelving"
    End With
    Range("A2:A" & Lastrow).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True

    As the above, which seems like it should work, is instead hiding EVERYTHING.

    I'm so confused and know just enough about VB to be dangerous. Help? (And many thanks!)

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Hide Rows with a Value (2 values to hide) then delete visible rows

    I could be wrong but I think maybe it should be:
    "<>Available"
    not
    "=<>Available"

  3. #3
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    Excel 2016
    Posts
    313

    Re: Hide Rows with a Value (2 values to hide) then delete visible rows

    could you not do:
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Last edited by Leon V (AW); 08-07-2013 at 11:37 AM.

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Hide Rows with a Value (2 values to hide) then delete visible rows

    I think they want to delete everything without those words so it would be:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    Excel 2016
    Posts
    313

    Re: Hide Rows with a Value (2 values to hide) then delete visible rows

    Quote Originally Posted by yudlugar View Post
    I think they want to delete everything without those words so it would be:
    Please Login or Register  to view this content.
    edited in. I forgot that when you use filter you de-select rather than select strings

  6. #6
    Registered User
    Join Date
    05-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Hide Rows with a Value (2 values to hide) then delete visible rows

    Thanks! I tried to use

    'delete rows without Available or Reshelving in column 6
    Dim Lastrow As Long
    dim cell as range
    Application.ScreenUpdating = False
    Lastrow = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    for each cell in Range(cells(1,6),cells(lastrow,6)).cells
    if not cell.text = "Available" then cell.EntireRow.Delete
    if not cell.text = "Reshelving" then cell.EntireRow.Delete
    next cell

    But it threw an error.

    Run-time error '424':
    Object Required

    Thanks again for the help!

  7. #7
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    Excel 2016
    Posts
    313

    Re: Hide Rows with a Value (2 values to hide) then delete visible rows

    Sorry that was my fault (you can't do an if statement on a cell that was deleted in the previouse if statement)

    try this instead

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Hide Rows with a Value (2 values to hide) then delete visible rows

    That one seems to be deleting rows that contain "Reshelving." It's not doing anything else.

  9. #9
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    Excel 2016
    Posts
    313

    Re: Hide Rows with a Value (2 values to hide) then delete visible rows

    when I ran it it deleted both is Available the only thing in the cell or are there other characters?

    in the mean time try
    Please Login or Register  to view this content.
    Or
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    05-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Hide Rows with a Value (2 values to hide) then delete visible rows

    Yes! Sorry it took me so long to get back. Work's been... crazy. Yes, there are about 5 or 6 different things that can be in that field and I wanted to keep only the things tagged "Available" or "Reshelving."

    Good news is, the first one you posted in the most recent reply did what I'm looking for. Thank you, thank you, thank you!

  11. #11
    Registered User
    Join Date
    05-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Hide Rows with a Value (2 values to hide) then delete visible rows

    I feel really needy but how do I keep my headers?

  12. #12
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    Excel 2016
    Posts
    313

    Re: Hide Rows with a Value (2 values to hide) then delete visible rows

    Please Login or Register  to view this content.

+ 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. Delete vs Hide rows code not working
    By cxc300cxc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-22-2013, 03:41 PM
  2. Hide / Delete rows based on value in cell
    By lgimnich in forum Excel General
    Replies: 2
    Last Post: 06-30-2012, 09:52 AM
  3. Using check boxes to hide and un-hide rows with drop down lists within rows
    By Sparky_Chris in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2012, 07:22 AM
  4. Can't Delete Hide Rows
    By mohan.r1980 in forum Excel General
    Replies: 4
    Last Post: 01-31-2012, 06:42 AM
  5. Automatically hide or delete empty rows after IF ?
    By Lost! in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-02-2005, 05:13 PM

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