Auot hide rows with no data

[neal.clarke182]

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

 

[wigi]

Hmmmm.

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]

Sorry, double post.

 

[wigi]

Can you please use

 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.