using a macro to hide empty rows

[BSGP8NTBALL]

I have turned into the company excel guy and I am very limited in my macro knowledge. I have already signed up for some classes but they don't begin until after the holidays. I want to hide a row if there is no data inputed in that row. I have searched and read other macro's that seem to do close to the same thing. Is this possible? Can someone help to point me to where I could find an example of this macro or is it easy enough for some to just "whip" it up in a few lines? Thanks ahead for your time/

 

[Von Pookie]

Would this work for you?

Sub test()
Dim LastRow As Long, i As Long

LastRow = Range("A65536").End(xlUp).Row

For i = 1 To LastRow
    If Application.CountA(Rows(i)) = 0 Then
        Rows(i).Hidden = True
    Else
        Rows(i).Hidden = False
    End If
Next i

End Sub

 

[Andrew Poulsom]

An alternative:

Sub Test()
    Dim Rng As Range
    Dim Cell As Range
    Set Rng = ActiveSheet.Columns(1).SpecialCells(xlCellTypeBlanks)
    For Each Cell In Rng
        If WorksheetFunction.CountBlank(Cell.EntireRow) = Cell.EntireRow.Columns.Count Then
            Cell.EntireRow.Hidden = True
        End If
    Next Cell
End Sub

 

[BSGP8NTBALL]

wow, thanks for the quick responses guys. I used the first formula but I found out that I needed atlittle more. I have created a form and in the first colum I have descriptions, then I have about 11 fields to enter numbers of drawings. they total at the end of the row for a total drawing count. With the formula (basic =sum thing) there is always a zero. Can the hide row function be dependant on the "AZ" colum being = to 0? This was if I have any drawing totals in the colum they are not hidden. I know this sounds kinda picky but I guess i didn't know exactly what I needed until I plugged the last one in. Thanks again. Hopefully after my classes I can actually get in here and help some people like you guys have done for me.

 

[Von Pookie]

Can the hide row function be dependant on the "AZ" colum being = to 0?

No problem, just make a small change to the code:

Sub test()
Dim LastRow As Long, i As Long

LastRow = Range("A65536").End(xlUp).Row

For i = 1 To LastRow
    If Cells(i, "AZ") = 0 Then
        Rows(i).Hidden = True
    Else
        Rows(i).Hidden = False
    End If
Next i

End Sub

 

[just_jon]

Can also Autofilter on that column.

 

[Von Pookie]

That's true--it <strike>would</strike> should speed things up a little since it doesn't use a loop.

Edit: Removed the code since it wouldn't really help. I just now realized/remembered these rows were supposed to be *hidden*, not deleted

 

[BSGP8NTBALL]

data actually starts in field az23, I can see that I shoud just switch out the field number but what does the autofilter field =52 do? I am getting an error when i run the macro. Runtime error 1004 autofilter method of range class failed. Also, is this autofilter macro deleting the rows or hidding them?

 

[Andrew Poulsom]

data actually starts in field az23, I can see that I shoud just switch out the field number but what does the autofilter field =52 do? I am getting an error when i run the macro. Runtime error 1004 autofilter method of range class failed. Also, is this autofilter macro deleting the rows or hidding them?

field:=52 means use column 52. That would be right if columns A:AZ were AutoFiltered, but the posted code AutoFilters only column AZ, so field:=1.

 

[BSGP8NTBALL]

alright I have tried the before posted macros and have had no luck at all getting them to run. I tried to make the changes I thought worked but it didn't seem to help. This is what I have so far.

Sub HideRows()
'

Dim EndNum As Integer

EndNum = Range("az23:az" & Range("az65536").End(xlUp).Row).Rows.Count


For i = 23 To EndNum
Range("az" & i).Select
If ActiveCell.Value = 0 Then
Selection.EntireRow.Hidden = True

End If
Next i

End Sub



The only problem I am having is that the macro stops hidding the rows in row 160. I have data down to row 183 which tells me the "23" is stopping it but when I try to change that number I get errors. Maybe debugging a macro that is mostly working will be easier. I understand it must me hard to visualize what I am trying to do. Thanks again for everyones help.