Macro To Hide Rows with no data

[McDan1el]

Hi All,

Im building a new holiday database for my staff and to make it easier the database shows records of everyday they have booked off for the years, 01/01/08-31/12/08 i've built an external file called HolidayCard for Team Leaders to easily see what holiday their team members have, now it links to the database fine, but has massive gaps where it lists all 365 days.

Is their a macro i can run or a forumla to hide the cells that dont have holiday booked and unhide them if they do?

I found some code on here (forgive me but i cant find the link now) which almost worked but i think my forumales may be causing issues, as its basically if HolidayDatabase has nothing booked, Then "", otherwise show the holiday booked - i think usinga "" null command causes issues.

Any help appreciated.

Dan

 

[Norie]

Dan

Couldn't you use Data>Filter...?

 

[McDan1el]

Im quite new to excel, so im not sure?

What will this do?

 

[Peter_SSs]

Im quite new to excel, so im not sure?

What will this do?

McDan1el

Welcome to the Mrexcel board!

Can you tell us about the layout/headings/formulas etc on your sheet? That would make it a bit easier to suggest a possible solution.

 

[McDan1el]

Ok the holiday card is designed to link everyday from the holiday database and will output the date, times and total hours of holiday for any given agent

So

01/01/07 09:00-17:00 7 Hours


to get the date its:

=IF('[Holiday Database.xls]KerryLloyd'!$C22=0,"",'[Holiday Database.xls]KerryLloyd'!$B22)

C22 is a VLookUp so either gives a time (09:00-17:00 for example) or 0 if it doesnt exist, so if its 0 there is no holiday booked so make it "", otherwise paste the date.

So

E26:J391 = 365 days i want it to hide when there is no holiday booked so when it produces a "" value.

Hoep this helps

/edit

I found the code that almost worked:

Sub HideBlankRows()
Dim LastRow As Long, I As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For I = 26 To 391
If WorksheetFunction.CountA(Rows(I)) = 0 Then Rows(I).EntireRow.Hidden = True
Next I
Application.ScreenUpdating = True
End Sub


i changed For I = 26-391 to target those rows, now it works if there is no forumal in the rows, but with the forumals it doesnt

 

[McDan1el]

Sub HideRows()
On Error Resume Next
With Range("e25:j391")
.EntireRow.Hidden = False
For I = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(I)) = "" Then
.Rows(I).EntireRow.Hidden = True
End If
Next I
End With
End Sub


The above almost works, only it hides everything in that range! :/

 

[Peter_SSs]

Still not really clear on the layout, but I'm wondering if you really need a macro?

Would this be any good?
1. In K26 (or some other unused colum if K is not suitable), put the formula: =LEN(E26&F26&G26&H26&I26&J26)>0
2. Copy the formula down to row 391.
3. Put a heading (maybe "Booked") in cell K25.
4. Select K25 and then from the menus: Data|Filter|AutoFilter
5. Now click the drop-down on K25 and choose TRUE.
(6. You can use the drop-dow again to show (All), or from the menus Data|Filter|Show All)

If you really need a macro:
a) Would a filter method like this be OK?
b) What would trigger the macro to run?

 

[McDan1el]

After some more digging i got a working macro which i've assigned to a "Update" Button

Sub HideRows()
BeginRow = 26
EndRow = 391
ChkCol = 5

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub



Col 5 = Date, so if thats "" it hides for the above range.

Works fine!

 

[Peter_SSs]

May hardly be noticeable with only a few hundred rows like this, but looping through the rows one-by-one is relatively slow. Try this AutoFilter macro method as well.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> HideRows()
    Range("E25:E391").AutoFilter Field:=1, Criteria1:="<>"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>