• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Macro to hide columns beginning with the text "freight"|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Macro to hide columns beginning with the text "freight"|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosMacro to hide columns beginning wit…
sp_PrintTopic sp_TopicIcon
Macro to hide columns beginning with the text "freight"
Avatar
eric lau
Member
Members
Level 0
Forum Posts: 8
Member Since:
May 8, 2019
sp_UserOfflineSmall Offline
1
November 1, 2019 - 12:32 am
sp_Permalink sp_Print

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 

sp_AnswersTopicSeeAnswer See Answer
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
November 1, 2019 - 2:40 am
sp_Permalink sp_Print

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

Avatar
eric lau
Member
Members
Level 0
Forum Posts: 8
Member Since:
May 8, 2019
sp_UserOfflineSmall Offline
3
November 6, 2019 - 10:51 pm
sp_Permalink sp_Print

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!

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
November 7, 2019 - 10:38 am
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
eric lau
Member
Members
Level 0
Forum Posts: 8
Member Since:
May 8, 2019
sp_UserOfflineSmall Offline
5
November 12, 2019 - 12:42 am
sp_Permalink sp_Print

Thank you Sunny this works perfectly. 

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
6
November 13, 2019 - 1:56 pm
sp_Permalink sp_Print

Smart way out of complicate issues  always, Sunny!!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
November 13, 2019 - 2:29 pm
sp_Permalink sp_Print

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

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
8
November 14, 2019 - 11:06 am
sp_Permalink sp_Print

Yes the define is precise.

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 613
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
9
November 14, 2019 - 10:09 pm
sp_Permalink sp_Print

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

Avatar
David_Ng
Member
Members
Level 0
Forum Posts: 306
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
10
November 15, 2019 - 4:09 pm
sp_Permalink sp_Print

Agreed, simplest way out!

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
11
November 15, 2019 - 4:38 pm
sp_Permalink sp_Print

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Chandler Davis
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27210

 

Member Stats:
Guest Posters: 49
Members: 31878
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.