using a macro to hide empty rows

BSGP8NTBALL

New Member
Joined
Nov 23, 2005
Messages
14
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/
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Would this work for you?

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
An alternative:

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

New Member
Joined
Nov 23, 2005
Messages
14
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686

ADVERTISEMENT

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

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

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
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686

ADVERTISEMENT

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 :rolleyes: :oops:
 

BSGP8NTBALL

New Member
Joined
Nov 23, 2005
Messages
14
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
BSGP8NTBALL said:
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

New Member
Joined
Nov 23, 2005
Messages
14
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,665
Members
412,481
Latest member
nhantam
Top