Log In
Register
Home
May 8, 2019
Offline
Hello,
I am trying to create a macro to hide every row that displays "freight" or any line with freight attached to it in a certain column. The reason i am doing this is because i don't need to see any freight costs associated with the items purchased. I would like to create a loop and some if-then statements to accomplish this but i have no idea how to start it.
So for the below example, i would like to hide row 1,3,4. Of course, my data set is larger than this. Any help with this topic would be much appreciated. Thanks.
| 9500 | freight-inbound |
| 7450 | cargo |
| 2050 | freight-inbound |
| 334 | freight-inbound |
| 56498 | inventory |
| 564564 | inventory |
-Eric
See Answer
VIP
Trusted Members
June 25, 2016
Offline
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
May 8, 2019
Offline
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!
VIP
Trusted Members
June 25, 2016
Offline
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
Answers Post
May 8, 2019
Offline
Thank you Sunny this works perfectly.
December 5, 2016
Offline
Smart way out of complicate issues always, Sunny!!
November 8, 2013
Offline
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))
LastRow = ThisWorkbook.Worksheets("Sheet2").Cells.Find("*", ThisWorkbook.Worksheets("Sheet2").Cells(1), , , xlByRows, xlPrevious).Row
For r = 1 To LastRow 'loop thru each row
If InStr(UCase(Cells(r, 6)), UCase(x)) > 0 Then
If HideRng Is Nothing Then
Set HideRng = Cells(r, 6)
Else
Set HideRng = Union(HideRng, Cells(r, 6))
End If
End If
Next
If Not HideRng Is Nothing Then HideRng.EntireRow.Hidden = True
December 5, 2016
Offline
Yes the define is precise.
Trusted Members
Moderators
November 1, 2018
Offline
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".
December 5, 2016
Offline
Agreed, simplest way out!
November 8, 2013
Offline
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.
1 Guest(s)
