Forum

Macro to hide colum...
 
Notifications
Clear all

Macro to hide columns beginning with the text "freight"

11 Posts
5 Users
0 Reactions
79 Views
(@gumsaiga)
Posts: 8
Active Member
Topic starter
 

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 

 
Posted : 01/11/2019 10:32 am
(@sunnykow)
Posts: 1417
Noble Member
 

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

 
Posted : 01/11/2019 12:40 pm
(@gumsaiga)
Posts: 8
Active Member
Topic starter
 

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!

 
Posted : 07/11/2019 8:51 am
(@sunnykow)
Posts: 1417
Noble Member
 

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

 
Posted : 07/11/2019 8:38 pm
(@gumsaiga)
Posts: 8
Active Member
Topic starter
 

Thank you Sunny this works perfectly. 

 
Posted : 12/11/2019 10:42 am
(@david_ng)
Posts: 310
Reputable Member
 

Smart way out of complicate issues  always, Sunny!!

 
Posted : 13/11/2019 11:56 pm
(@catalinb)
Posts: 1937
Member Admin
 

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

 
Posted : 14/11/2019 12:29 am
(@david_ng)
Posts: 310
Reputable Member
 

Yes the define is precise.

 
Posted : 14/11/2019 9:06 pm
(@debaser)
Posts: 837
Member Moderator
 

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".

 
Posted : 15/11/2019 8:09 am
(@david_ng)
Posts: 310
Reputable Member
 

Agreed, simplest way out!

 
Posted : 16/11/2019 2:09 am
(@catalinb)
Posts: 1937
Member Admin
 

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.

 
Posted : 16/11/2019 2:38 am
Share: