Deleting hidden rows

Ricalsin. · May 5th 2006

Hello,

Inexperienced VBA guy (okay with C#.)

Question 1: Why do I get a "sub or function not defined" error on the first line? The problem seemed to arise out of nowhere. The sub is located in module 1. I call it with "Call DeleteHiddenRows" in sheet1 inside of a "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" event. Should I be adding some declaration somewhere (some "dim" line?)???

Code

Sub DeleteHiddenRows()
    For j = ActiveCell.SpecialCells(xlLastCell).Row To 1 Step -1
        If Rows(j).Hidden Then
            Rows(j).Hidden = False
            Row(j).Activate
            Selection.Delete
        End If
    Next j
End Sub

Rick

Ricalsin. · May 5th 2006

Re: Deleting hidden rows

Answer? When I comment out the two lines the sub functions without the error. This leads me back to my original problem, which is that I'm not getting the rows to delete. Obviously, I've got the wrong code. Any suggestions?

Code

Sub DeleteHiddenRows()
    For j = ActiveCell.SpecialCells(xlLastCell).Row To 1 Step -1
        If Rows(j).Hidden Then
            Rows(j).Hidden = False
'            Row(j).Activate
'            Selection.Delete
        End If
    Next j
End Sub

Rick

Ricalsin. · May 6th 2006

Re: Deleting hidden rows

Hello again,

I have the sub running now, but it does not progress down the page like I think it should. Here's what I have now:

Code

Sub DeleteHiddenRows()
    For j = 1 To 2542
        If Rows(j).Hidden Then
            Rows(j).Hidden = False
            Rows(j).Select
            Selection.EntireRow.Delete
        End If
    Next j
End Sub

Is the deletion aspect messing up the progression of the "1 to 2542" count?

The bigger picture is that 27-row segments display product info. In the first run through the page the macros hide each 27-row section if the product is not in inventory. Usually, there is about 10 - 20 products in inventory (270 - 540 rows left unhidden) for any given number it is set to search. I was hoping to email these results but the "save as web page" option adds memory for the hidden rows. So, I'm trying to run through the page again and delete the rows that were hidden in the first run.

Can anyone see why this code does not accomplish this?

Rick

Insomniac · May 6th 2006

Re: Deleting hidden rows

The 1st code you posted was geting the error at Row(j).Activate, needs to be Rows not Row.

You do need to loop backwards when deleting this way to catch all the rows.
Also you dont need to Activate/Select anything.

Code

Sub DeleteHiddenRows()
    For j = ActiveCell.SpecialCells(xlLastCell).Row To 1 Step -1
        If Rows(j).Hidden Then
            'Rows(j).Hidden = False
            'Rows(j).Activate
            'Selection.Delete
            Rows(j).EntireRow.Delete
        End If
    Next j
End Sub

The looping may get a bit slow over a large range but you can do it without looping, something like:

Code

Sub delHiddenRows()
Dim r As Range, k As Range
    With ActiveSheet
        Set r = .Range("A1:A" & .Cells.SpecialCells(xlCellTypeLastCell).Row)
        Set k = r.SpecialCells(xlCellTypeVisible)
        r.EntireRow.Hidden = False
        k.EntireRow.Hidden = True
        r.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        r.EntireRow.Hidden = False
    End With
End Sub

Display More

Ricalsin. · May 6th 2006

Re: Deleting hidden rows

Insomniac,

My looping version was not progressing through the rows even after your 'commented out' suggestion. I'm still not sure why. But, your non-looping proceedure works beautifully and I am so happy to have this issue solved. I still don't quite understand the code, but I'll study up on it.

Thanks for taking the time to help me. I'll do my homework.

Rick:smile: :smile: :smile:

falconfox · Aug 1st 2012

Re: Deleting hidden rows

Quote from Insomniac;256476

The 1st code you posted was geting the error at Row(j).Activate, needs to be Rows not Row.

Code

Sub delHiddenRows()
Dim r As Range, k As Range
    With ActiveSheet
        Set r = .Range("A1:A" & .Cells.SpecialCells(xlCellTypeLastCell).Row)
        Set k = r.SpecialCells(xlCellTypeVisible)
        r.EntireRow.Hidden = False
        k.EntireRow.Hidden = True
        r.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        r.EntireRow.Hidden = False
    End With
End Sub

Display More

I'm trying to use the above code after performing an advanced filter, but get an error "Run-time error '1004': no cells were found. The debug highlights this row: "r.SpecialCells(xlCellTypeVisible).EntireRow.Delete"

i'm filtering through a list with 1,000,000 entries, so speed is critical. That is why i wanted to use this code that doesn't loop.

I'm using Excel 2010 if that helps.

Deleting hidden rows

Participate now!

Share