Auot hide rows with no data

neal.clarke182

New Member
Joined
Jan 19, 2012
Messages
10
Hi All,

Basically I have the below chart pulling through data as listed by each column header from various parts of oth
er worksheets. I have the system set to that is there is no tracker reference number, it will not pull though to the segment of sheet as displayed below. Obviously that all the cells have a vlookup in them as formulas but do not actually display any data unless the criteria I have selcted is fulfilled.
What I am trying to do is, autohide using a macro or via VBA any rows that have no data in. So in the example below I have 4 lines that need to be hidden, and then the two with data in will be at the top of my list and so on after that giving one long list, rather than having huge gaps in it.

I have also give the best attempt that I have had / found else where to try to make this work using some VBA code.

Any assistance will be greatly apreciated. Thanks in advance.
Tracker Reference Number
Date of Enquiry
Person Taking Enquiry
Enquiry Method
Name of Parent or Carer
Name of Child
Age of Child at start
Date of Start
Sessions Required
24
9/12/2013
DC
Email
Daughter
24 months
January 2014
wants 3 sessions told min 4
25
14/10/2013
EH
Email
Daughter
16 months
Unknown
Unknown

<tbody>
</tbody>









Sub way()
Dim r As Range
Dim nLastColumn As Long
Dim nLastRow As Integer
Dim i As Integer
Dim HideIt As Boolean
Dim j As Long
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nLastColumn = r.Columns.Count + r.Column - 1
For i = 1 To nLastRow
HideIt = True
For j = 2 To nLastColumn
If Cells(j, i).Value <> "" Then
HideIt = False
End If
Next
If HideIt = True Then
Rows(i).EntireRow.Hidden = True
End If
Next
End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hmmmm.

Code:
Sub wigi()

    Application.ScreenUpdating = False
    For Each r In Cells(1).CurrentRegion.Rows
        If WorksheetFunction.CountIf(r, ">""""") = 0 Then r.EntireRow.Hidden = True
    Next


End Sub
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Sorry, double post.
 
Last edited:

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Can you please use
Code:
 tags when you paste code on the forum?
Code tags format the code making it easier to read and hence follow the logic of the code.


You can use [CODE] tags in this way: 


Add the word [color=blue][b][PLAIN][code=rich][/PLAIN][/B][/color] before the first line of code, and
add the word [color=blue][b][PLAIN]
[/PLAIN][/B][/color] after the last line of code.


Or: you could use the "#" icon when changing / composing a message in the Advanced editing screen.


Thanks for your consideration.
 

Forum statistics

Threads
1,136,352
Messages
5,675,280
Members
419,559
Latest member
BraytonM

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