Hi Eric

Instead of a macro, would you consider using a helper column instead to identify the rows containing the text "freight"?

You could then filter the helper column.

Something like =IFERROR(SEARCH("freight",B2),"")

You can then filter only those blank cells.

Hope this helps.

Sunny

Hello Sunny,

The reason i don't want to do that is because i need to present it to my boss. I could always use the helper column and delete all the lines presented but i need to do this on a daily basis. I was able to come up with a simple macro that does this:

Sub HideEmptyRows()

      Dim cell As Range

    Application.ScreenUpdating = False

    For Each cell In Range("A1:O1000")
    If cell.Value = "freight-inbound" Then
            cell.EntireRow.Hidden = True
        End If
    Next cell
    Application.ScreenUpdating = True
End Sub

However the range always changes.. so i  have to manually adjust the range.. Is there a way to soft code it so it selects all the text or at the very least scrolls to the bottom? My files has spaces between the rows so how do i accomodate for that?

Thanks!

Hi Eric

Give this a try. I am assuming only number of rows will change and the columns are fixed at 15.

The macro will check for the last row using column A.

Sub HideRows()
Dim r As Long
Dim c As Long
Dim LastRow As Long
Dim x As String
Dim Cols As Long

x = "FREIGHT"
Cols = 15 'number of columns, O=15
LastRow = Cells(Rows.Count, "A").End(xlUp).Row 'check for last row in column A
Application.ScreenUpdating = False
For r = 1 To LastRow 'loop thru each row
For c = 1 To Cols 'loop thru each cell in a row
If InStr(UCase(Cells(r, c)), UCase(x)) > 0 Then
Cells(r, c).EntireRow.Hidden = True
End If
Next
Next
Application.ScreenUpdating = True
End Sub

Sunny

Thank you Sunny this works perfectly. 

Smart way out of complicate issues  always, Sunny!!

One minor observation:
End(xlup) is not 100% reliable, if for example the entire column A has a color or borders, even if cells are empty this method will return the last formatted row, not the last non empty row.

It fails in defined tables also.

I use Cells.Find, works in all scenarios.

Also, I suggest to avoid doing operations row by row, it has an important impact on speed.

Instead, collect the cells that match criteria and hide all matching rows in a single operation, outside the loop, it's much faster.

Because the code is placed in a normal module, not in a sheet module, fully qualified references should be used, unless you want the code to work on any sheet. (not just Cells(1,1), but: ThisWorkbook.Worksheets("Sheet2").Cells(1,1))

Yes the define is precise.

I still can't see why an autofilter wouldn't suffice if you're only looking in one column (as the original post says, although the subsequent code implies otherwise). You don't even need a helper column, just filter for "does not contain".

Agreed, simplest way out!

Velouria said
I still can't see why an autofilter wouldn't suffice if you're only looking in one column (as the original post says, although the subsequent code implies otherwise). You don't even need a helper column, just filter for "does not contain".  

There is a file uploaded by Eric in another post, it's about (wrong) data structure. He has multiple sections with duplicate headers, sections are separated with multiple blank rows.

This should explain all problems, all requirements are generated by a poor data structure, and this usually leads to more and more complex solutions needed to respond to simple questions/reports.