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/
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,356
Messages
6,136,078
Members
449,988
Latest member
Mabbas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top