Delete Specific Rows

[Pat_The_Bat]

Hi Friends,

I am writing a macro that resets the sheets I'm working with. On one page it simply clears contents of the columns I use, but on the other sheet I need to actually delete all rows except for the ones I've defined.

So I want to delete all rows from row 2 down to LstRow
But don't delete the following rows :

IncomeHDRAK
HDRAK2
HDRAK3
HDRAK4
HDRAK5

Dim IncomeHDR As Integer
Dim HDRAK2 As Integer
Dim HDRAK3 As Integer
Dim HDRAK4 As Integer
Dim HDRAK5 As Integer


With Sheets("Doc Request")
        .Range("B4:B499").ClearContents
        .Range("Z4:Z499").ClearContents
End With


Sheets("Doc List").Activate
        With Sheets("Doc List")
            
            
            Set IncomeHDRAK = Range("AK1:AK5").Find("Income")
            IncomeHDR = IncomeHDRAK.Row
            Debug.Print IncomeHDR
                        
            Set HDRAK2 = Range("AK:AK").Find("ASSET")
            HDR2 = HDRAK2.Row
            Debug.Print HDR2
            
                        
            Set HDRAK3 = Range("AK:AK").Find("REO")
            HDR3 = HDRAK3.Row
            Debug.Print HDR3
                        
            Set HDRAK4 = Range("AK:AK").Find("Credit")
            HDR4 = HDRAK4.Row
            Debug.Print HDR4
            
            Set HDRAK5 = Range("AK:AK").Find("Other")
            HDR5 = HDRAK5.Row
            Debug.Print HDR5
            
            Dim LstRow2 As Integer
            LstRow2 = Range("F" & .Rows.Count).End(xlUp).Row
            Debug.Print LstRow
            
        End With

 

[Yongle]

Your code would have failed - your variables were declared as integers but you were trying to use them as ranges
If using With then the range needs preceding with ". " (used 3 times in amended code)

This deletes every row starting at row 2
Note that rows are deleted from the bottom (prevents row numbers that VBA about to use changing as rows deleted)
This will work even if any of the values are not found

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim IncomeHDR As Long
    Dim HDRAK2 As [COLOR=#ff0000]Range[/COLOR]
    Dim HDRAK3 As Range
    Dim HDRAK4 As Range
    Dim HDRAK5 As Range
    Dim DoNotDelete As Range, rng As Range
    Dim LstRow2 As Long, r As Long, x As Variant
    
    With Sheets("Doc Request")
            .Range("B4:B499").ClearContents
            .Range("Z4:Z499").ClearContents
    End With

    With Sheets("Doc List")
        Set rng = [COLOR=#ff0000][B].[/B][/COLOR]Range("AK:AK")
        Set IncomeHDRAK = rng.Find("Income")
        Set HDRAK2 = rng.Find("ASSET")
        Set HDRAK3 = rng.Find("REO")
        Set HDRAK4 = rng.Find("Credit")
        Set HDRAK5 = rng.Find("Other")
[COLOR=#696969]'create array of found cells[/COLOR]
        Set DoNotDelete = [B][COLOR=#ff0000].[/COLOR][/B]Range("AK1")
        For Each x In Array(IncomeHDRAK, HDRAK2, HDRAK3, HDRAK4, HDRAK5)
            If Not x Is Nothing Then Set DoNotDelete = Union(DoNotDelete, x)
        Next x
[COLOR=#696969]'delete rows[/COLOR]
        LstRow2 = .Range("F" & .Rows.Count).End(xlUp).Row
        For r = LstRow2 To 2 Step -1
            If Not Intersect(.Range("AK" & r), DoNotDelete) Is Nothing Then
                ' retain this row - do nothing
            Else
                [B][COLOR=#ff0000].[/COLOR][/B]Rows(r).EntireRow.Delete
            End If
        Next
    End With
End Sub