CODEWITHSUNDEEP
excelvbaexcel-2010
Chris
Chris

Reputation: 3

How to delete a row instead of hiding it?

Trying to delete, rather than hide from one worksheet (where the filter button is to another where the catalogue list is. i.e. trying to create something like (best way to describe this) a shopping basket based upon the users selection and reducing the (long) list on the other worksheet after selection.

I am beginning to pull my hair out on this one and after having read and watched many, many articles on deleting rows after filtering on empty cells and today nothing has really helped as there are more issues than solutions when following codes from other, thus far.

Basically, I have a Hugh catalogue on a separate worksheet and if the user says yes, this should be shown and if not if blank (but with a value) it should be deleted. See so very basic script I have that works perfectly, but I have to delete and not hide. It appears EntireRow.delete is something beyond me, as it introduces many many issues, where hide simply worked so smoothly.

Trying many other scripts, they all really fail in simplifying the answer and 99% are actually for a single worksheet and range rather than a specific worksheet and specific columns i.e. E:E (script script below shows more) I am using a table too, so this is a little different too.

For a = 2 To 150
    If Worksheets("Requirements").Cells(a, 5).Value = "High" Then
        Worksheets("Requirements").Rows(a).Hidden = True
    End If
Next

Anyone with a brilliant one or two liner to delete rather than hide, or delete all hidden if necessary

Many thanks in advance

Upvotes: 0

Views: 148

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Sub sjdhfs()
    For a = 150 To 2 Step -1
        If Worksheets("Requirements").Cells(a, 5).Value = "High" Then
            Worksheets("Requirements").Cells(a, 5).EntireRow.Delete
        End If
    Next a
End Sub

Note we run the loop from the bottom to the top.

Upvotes: 2

Related Questions