Macro To Hide Rows with no data

McDan1el

Board Regular
Joined
Sep 14, 2007
Messages
89
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Dan

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 14, 2007
Messages
89

ADVERTISEMENT

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

Board Regular
Joined
Sep 14, 2007
Messages
89
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Sep 14, 2007
Messages
89
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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
Top