Delete rows

[dcwinter]

Hi everyone!

It's been a while since I did a lot on Excel so I'm rusty again. I have the following code which I thought was sufficent, but for some reason it is not deleting the hidden rows. Can anyone spot any issues? (I've shortened the code because of repetition):

Sub Macro9()
Sheets("Footfall raw data").Select
Range("E1").Select
Dim myRng As Range
Set myRng = Range("E1", Range("E5000").End(xlUp))
Application.ScreenUpdating = False
For Each c In myRng
If c.Value = " Card/ID Reset" Then c.EntireRow.Hidden = True
Next c
For Each c In myRng
If c.Value = " Clock In/Out" Then c.EntireRow.Hidden = True
Next c

...

For i = 1 To [A65536].End(xlUp).Row
If Rows(i).Hidden = True Then
Rows(i).EntireRow.Delete
i = i - 1
End If
Next i
End Sub

Any help, as ever, greatly appreciated!

DC

 

[xladept]

Hi dcwinter,

Is there a protection issue?

 

[Michael M]

Try

Sub test()
Dim i As Long
    For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
        If Rows(i).Hidden = True Then Rows(i).Delete
    Next i
End Sub

 

[Trebor76]

Hi DC,

If you want to delete all rows that either contain "Card/ID Reset" or "Clock In/Out" from column E in the "Footfall raw data" tab, there's no need to hide the rows first and then delete the hidden rows. You can do the whole process in one go via the following:

Option Explicit
Sub Macro2()

    'http://www.mrexcel.com/forum/showthread.php?643381-Delete-rows
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
        
    lngMyCol = Sheets("Footfall raw data").Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Sheets("Footfall raw data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
    Application.ScreenUpdating = False
    
    With Sheets("Footfall raw data").Columns(lngMyCol)
        With Range(Sheets("Footfall raw data").Cells(1, lngMyCol), Sheets("Footfall raw data").Cells(lngMyRow, lngMyCol))
            .Formula = "=IF(OR(TRIM(E1)=""Card/ID Reset"",TRIM(E1)=""Clock In/Out""),""DEL"","""")"
            .Value = .Value
        End With
        .Replace "DEL", "#N/A", xlWhole
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    Application.ScreenUpdating = True

End Sub

Just make sure to initially run the macro on a copy of the tab as the results cannot be undone if they're not as expected.

HTH

Robert

 

[dcwinter]

Hi DC,

If you want to delete all rows that either contain "Card/ID Reset" or "Clock In/Out" from column E in the "Footfall raw data" tab, there's no need to hide the rows first and then delete the hidden rows. You can do the whole process in one go via the following:

Option Explicit
Sub Macro2()

    'http://www.mrexcel.com/forum/showthread.php?643381-Delete-rows
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
        
    lngMyCol = Sheets("Footfall raw data").Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Sheets("Footfall raw data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
    Application.ScreenUpdating = False
    
    With Sheets("Footfall raw data").Columns(lngMyCol)
        With Range(Sheets("Footfall raw data").Cells(1, lngMyCol), Sheets("Footfall raw data").Cells(lngMyRow, lngMyCol))
            .Formula = "=IF(OR(TRIM(E1)=""Card/ID Reset"",TRIM(E1)=""Clock In/Out""),""DEL"","""")"
            .Value = .Value
        End With
        .Replace "DEL", "#N/A", xlWhole
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    Application.ScreenUpdating = True

End Sub

Just make sure to initially run the macro on a copy of the tab as the results cannot be undone if they're not as expected.

HTH

Robert

Thanks for this. I had no idea how to do it. Sometimes I do end up going the long way around!

Worked perfectly first time too

DC

 

[Trebor76]

Thanks for the feedback and I'm glad it all worked out