Deleting hidden rows

  • 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

  • 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

  • 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

  • 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:

  • 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:

  • 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.



    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!