VBA Macro To Filter Rows By Cell Value & Hide Blank Columns
Oct 1, 2008
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.
basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.
the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.
where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
View 7 Replies
ADVERTISEMENT
Jul 28, 2014
I have a list of about 200 companies in column a. Columns B, C, D, E, etc. list revenues for 2005, 2006, 2007, etc. The problem is not all of the years have values. Is there a way to filter out the companies that have a blank cell for any of the years? For example, if company 1 has a blank in 2007 can I filter it out, even if all of the rest of the revenues are filled in?
View 2 Replies
View Related
Sep 15, 2007
Hide Multiple rows. I have text within two columns A, D. How can I Hide Blank rows which spans two column Named Ranges, "Range1" (A1:A15) "Range2" (D1:D15). As an example:
Beginning with this:
...A......D
1.Text
2........Text
3.
4.
5.
6.Text
7.Text
8.
9.
10.......Text
11.Text
12.
13.......Text
14.
15.......Text
To this , after hiding blanks:
...A......D
1.Text
2........Text
6.Text
7.Text
10.......Text
11.Text
13.......Text
15.......Text
View 2 Replies
View Related
Dec 21, 2008
I need to hide rows based on a cell value. In cell B4 I list how many students are in period one.
I have enough rows for 35 students found in A10:A44. In C10: C44 I listed numbers 1-35.
So I need any row that has a number in the C column higher than what was entered in B4 to be hidden, but when cell B4 changes I need rows to unhide if their row is less than new value.
So here was my attempt: ...
View 9 Replies
View Related
Jan 19, 2007
I have a row in a table from columns C to CZ. All the cells in the row contain a formula, between 1 and 204 cells in the row will have a value (i.e. will not be blank).
The cells with a value will start at column C and may or may not have a blank cell before no more values and blank cells to the end (Col GZ).
Example:
C D E F G H I J K >..........................GZ
23 34 67 74 2 34 6 2 56 all blank ("") to end
or:
C D E F G H I J K >..........................GZ
23 34 67 "" "" 34 6 all blank ("") to end
I need to hide the entire columns when the cells in this row are blank but NOT if the blank cell has valued cells after it (i.e. do not hide columns F and G in the second example.
I can do this by looping back from col GZ and hiding the columns one at a time, which is very slow. I am stuck on the code to select all the relavent columns and hide together.
View 4 Replies
View Related
Mar 9, 2014
I'm trying to create a table that adjusts dynamically to how many cells in a row have text in them. In other words, when a cell is left blank, the entire row should not appear in the table.
I've tried the following code:
Sub test()
Dim i As Integer
Dim nrrows As Integer
[Code]....
When I run this code, it hides all rows, even the ones that aren't blank. why this might happen and how I can make it work?
View 9 Replies
View Related
Aug 1, 2008
The macro code that will populate and input box and ask you which range of columns and range of rows you wish to hide, hide the columns and advise you via a message box that it has been completed
View 9 Replies
View Related
Feb 20, 2013
I have a worksheet that contains 15 instances of a repeated table over 700 rows. Each table is 45 rows in size and is housed in between the natural page breaks in the spreadsheet.
These tables are populated from data form another worksheet but may not all be used (8 out of the 15 may be used but will always start from table 1 and there will be no missed tables).
In the very top right of the table is a cell value that is only displayed if the table is in use, so will be blank if not used.
Code that will hide multiple rows (45) based on a cell value being blank.
View 2 Replies
View Related
Oct 8, 2009
I've tried using multiple loops in the forum but cannot seem to figure out how to actually get them to work properly using the conditional VBA codes on two separate worksheets. The first code snippet is checking cell values from row 6 to 148 as such:
Sub Check_Shifts()
'Insure all shift entries are completed
If Range("K6").Value < "1" And Range("I6").Value < "1" And Range("G6").Value < "1" Then
Range("G6").Value = Range("F6").Value
Range("I6").Value = Range("F6").Value
Range("K6").Value = Range("F6").Value
ElseIf Range("K6").Value < "1" And Range("I6").Value < "1" Then
Range("I6").Value = Range("G6").Value
Range("K6").Value = Range("G6").Value
ElseIf Range("K6").Value < "1" Then
Range("K6").Value = Range("I6").Value
End If
If Range("K7").Value < "1" And Range("I7").Value < "1" And Range("G7").Value < "1" Then........................
View 7 Replies
View Related
Dec 28, 2011
I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
View 5 Replies
View Related
Nov 28, 2007
I am trying to hide empty columns, excluding the header, from data that has been auto filtered. I can accomplish this when the data is not filtered by hiding columns that are empty below the header.
My question is: How do I get the "For each Col in Activesheet..." loop to only apply to the filtered values and not those that are hidden.
View 6 Replies
View Related
Nov 10, 2008
I have a parts index containing 500+ rows of data, I also have multiple columns in which I can sort the data.
I would like to have a button fixed in cell C4, D4, E4, etc... row 4 is the header, that when clicked, will filter all rows that have a selection (non-blanks) for only that column. So If I have data in various rows & column (C5:F500) that when I click on the button in cell D4, only the data listed in the various rows of D5:D500 are shown and columns C, E & F are hidden. Clicking cell D4 will show all data.
I can create the button so that it is sized specific to the cell and follows the cell sizing and I can create a button to filter only columns D or E or F, etc... but that means that each button is a separate VBA, macro.
A while ago I found a VBA code that allowed me to sort rows Asending/Desending depending on which button in which column was clicked... See inset: and this allowed me to add columns without changing the code. So I think If I can modify this code this will work great for me..
Sub SortTable()
'code written by Dave Peterson 2005-10-22
'2006-08-06 updated to accommodate hidden or filtered rows
Dim myTable As Range
Dim myColToSort As Long
Dim curWks As Worksheet
Dim mySortOrder As Long
Dim FirstRow As Long
Dim TopRow As Long
Dim LastRow As Long
Dim iCol As Integer
Dim strCol As String
Dim rng As Range
Dim rngF As Range
View 9 Replies
View Related
Jun 4, 2007
I have an Excel sheet with all client information...For clinical supervision I want my team members to open their own "Supervision.xls" and click a button...this button will open "Client.xls" and select "Client info sheet"...It will then hide rows c,d,g & f...it will then filter column "e" based on the specific caseworkers name ( say "Joe") and copy only the visable columns back to their "supervision.xls" Values only (doesn't effect the colour formating of Supervision.xls) then close "Client.xls"
View 9 Replies
View Related
Jun 4, 2007
I have an Excel sheet with all client information...For clinical supervision I want my team members to open their own "Supervision.xls" and click a button...this button will open "Client.xls" and select "Client info sheet"...It will then hide rows c,d,g & f...it will then filter column "e" based on the specific caseworkers name ( say "Joe") and copy only the visable columns back to their "supervision.xls" Values only (doesn't effect the colour formating of Supervision.xls) then close "Client.xls"
View 9 Replies
View Related
Oct 23, 2013
I Have Values in Cells A29 thru F45. I want to hide the entire row if the value in Column C is the same as the value in Column C in the row directly above, i.e, if the value in C30 is the same as C29, Row 30 should be hidden.
Based upon the logic built into the table values. if the value in C30 matched C29, all of the values from C30 thru C45 would match C29 with a need to hide all rows starting at Row 30 and then thru row 45. The values could begin repeating at Cell C31 with the value in C31 matching C30 with a need to hide from row 31 thru 45.
I am thinkin a Selection.AutoFilter type of macro?
View 9 Replies
View Related
Mar 25, 2009
Currently using this code to search Column D and hide if the cells are blank. How would I go about also hiding Column C as well, If D is blank?
I am using the spreadsheet as a grading template. The assignment name is under column C and the grade under column D. If there is no grade then I would like to be able to hide the unused assignment column along with the empty grade column.
Sub HideColumnsInd()
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Dim rCell As Range
For Each rCell In Range("D3:D48")
If rCell = "" Then
rCell.EntireColumn.Hidden = True
Else
rCell.EntireColumn.Hidden = False
End If
Next rCell
Application.EnableEvents = True
End Sub
View 9 Replies
View Related
Dec 7, 2011
I want to hide and unhide columns based on a cell's value.
If D6 of the ‘Summary’ Worksheet Is <> to ‘Test1’ and <> ‘Test2’
THEN Hide columns D:K of the ‘Charts – Source Data’ Worksheet
OTHERWISE Unhide columns D:K
View 4 Replies
View Related
Mar 8, 2014
I have a very large table and i need to be able to Hide/show specific ranges based on:
Filter +and+ specific cell values in columns
brief example of the table : tablee.png
So...
1. Filter Column "B" (in this case we select "HELPING")
2. Auto hide/show collumns. - IF "C1" = "Required" THAN Show "C:E", IF "C1" ="N/A" , HIDE "C:E" and so on for every column like above.
There are over 80 columns like the "C:E" range. and I only need to show those that are "Required".
View 1 Replies
View Related
Nov 3, 2006
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
View 2 Replies
View Related
Jun 17, 2003
Like to create a macro (button) to hide / unhide rows based on a cell value
Example
A1=No then hide roa A
C1=No then hide row c
View 9 Replies
View Related
Aug 22, 2013
I just want to Hide all the blank columns on the sheet apart from column A.
I already have this code, just dont know how to alter it:
VB:
Sub HideEmptyColumns() Dim c As Long
Dim n As Long
Application.ScreenUpdating = False
[Code].....
View 2 Replies
View Related
Jun 3, 2007
I have two spreadsheets. Spreadsheet B reads from the master. No entries are made on spreadsheet B, it is visual only. Changes are made only on the master. I want to create a Macro where all blank lines are filtered out automatically on spreadsheet B, in effect only lines with values are displayed.
Once a new task is created, the details are typed into the master spreadsheet and spreadsheet B, located in another building, is automatically updated to display the details. Once the task is completed which can be seconds, minutes or hours later, an "x" is placed in column A of the master. I have formulated spreadsheet B is "" all rows where "x" is listed in the master. Towards the end of the day however, I can have 50 visable entries on spreadsheet B spread over 500 rows.
I want spreadsheet B to display only active tasks from the top of the page down. I currently have those that read the spreadsheet filter out blank rows. Because tasks can be updated at a rate of 10 - 15 per minute with new tasks and the completion of old, I want to eliminate the need for our employees to have to filter themselves.
View 4 Replies
View Related
Mar 28, 2014
I extract data from a database and export it to excel, the data has A-E Columns.
Then I run a macro to perform calculations based on columns A-E,
The macro results in creating columns F - K. I have specified to run the macro on columns F-K.
Now in the future I would want to increase my database and add in more columns for example F-Hand this would directly effect my macro.
Is there a way to specify in the macro to conduct calculations that is "F-K" after "A-H" that is as soon as you hit a blank column/cell?
View 1 Replies
View Related
Sep 21, 2007
How do I go about writing VBA code to do the following:
1. Select a particular range of cells in column A
2. If one of these cells (in column A) returns a FALSE response to the Excel IsNonText formula to hide its entire row.
The circumstance is a little difficult to explain but let me know if you need clarification and I'll do my best.
View 6 Replies
View Related
Jan 2, 2014
I am trying to create a macro that will work on my master summary sheet. The sheet pulls information from other worksheets. I would like to have a macro that will look at range E7:E356. If the value is 0 then hide, but if the value changes then unhide the row. I have tried autofilter but it will not update automatically.
View 1 Replies
View Related
Mar 28, 2007
I want a macro to hid rows based on a specific value of a cell compared to another.
Example, In a cell I have the current date and time which updates when the the sheet is opened... On a row I have various info including another date in the past. When the date in the past goes over 6 months older than the current date, I want to hide that whole row.
Is this possible? Do I use an IF function in the macro I've seen to remove rows based on cells with specific values?
View 9 Replies
View Related
Jun 17, 2003
Like to create a macro (button) to hide / unhide rows based on a cell value
Example
A1=No then hide roa A
C1=No then hide row c
View 9 Replies
View Related
Feb 3, 2014
So I have a pivot table of about 2000 rows with data on employees.
In the table, there is a field associating each employee with a location (midwest, southeast,where ever) and I have a report filter on it that lets me view only people from a given region.
It was working fine, but I'm doing a lot of VBA macro related to the pivot, and I think I somehow changed a setting somewhere in the process and don't know how to reverse it.
When I apply the filter now, it only shows data for people in the region i choose, except it still shows all the other lines that should be hidden, just with no values. Here is a crude visual
Name region sales
Jim_____MW__10
Henry___MW__34
Jerry____SW__20
Aaron___SW__44
Scot____MW__22
AFTER FILTER for "MW"
Jim_____MW__10
Henry___MW__34
Jerry________0
Aaron_______0
Scot____MW__22
(jerry and aaron should not be visible at all)
View 2 Replies
View Related
May 8, 2008
I am developing a spreadsheet that, once all the code is run has numerous sheets added. On these sheets I have a significant number of rows that contain no data and could be hidden (I dont want to remove them, because later I need to re- import all these rows back to my master sheet). I tried code I found in the forum to hide a row if it is empty but this doesnt work as some of the cells contain formulaes referencing back to another sheet that is hidden.
I am trying, and failing, to write code to hide a row that contains no actual data, but still has formulas in some of the cells.
View 4 Replies
View Related
Oct 23, 2007
Can't seem to find a solution searching through various forums searches. I would like to hide all blank rows within a Named Range : Range1 (A1:E8).............
View 4 Replies
View Related
