VBA For Filter To Hide Colums & Rows

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


ADVERTISEMENT

Filter To Hide Rows If It Contains Blank Cells

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

Hide Or Filter Rows Based On Cell Value

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

Pivot Report Filter Doesn't Hide Non-Relevant Rows

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

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 View Related

Hide/Filter Rows Containing Formulas, But No Data. Empty Text

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

Rows To Colums

Aug 11, 2009

I have a sheet with products and information regarding these products. It is consistently build up in a 5-row information block per product like:

A1: name
A2: info
A3: price
A4: to be deleted
A5: to be deleted

What I want:

A1: name
B1: description
C1: Price

For product 2 it should:
A6 -> A2
A7 -> B2
A8 -> C2
A9 -> to be deleted
A10 -> to be deleted ..etcetera

The Macro should loop until no data is found in the A column. Note: some of the 2nd rows (information) are not filled. They can't be deleted but need to be left empty!

View 4 Replies View Related

Spreading A Value Over Multiple Colums/rows

Oct 15, 2008

I need to have a macro of formula that would take a cell's value (value1)and spread it over equally over a number (value2) of columns across

A B C D E ...
1 $300 3 $100 $100 $100
2 $20 4 $5 $5 $5 $5
etc.

The expectation is to place a formula like this:
=dividevalue(A1,A3) in Col3 and it does the rest magically!

View 9 Replies View Related

Identify Duplicates Across Multiple Colums And Rows

Dec 22, 2008

I have a spreadsheet with 20+ columns. Each column contains a list of invoice numbers, eg, 1234. The lists are all of different lengths (see below). I want to find a way to find/highlight/identify those invoice numbers that appear more than once in the entire sheet (eg if invoice 1234 is listed in column B, and also in column H, and/or if invoice 5678 is listed in column A, and also in column J, and also perhaps in column D, etc.)

I've searched this forum but haven't found anything about searching for multiple duplicates in multiple columns. Ideally the matches would be highlighted in different colours, (eg all cells that contain 1234 would be red, those that contain 5678 would be blue, etc., for ease of identification) but I'm not sure if that's possible. I've attached an example that I've shaded myself to show what I mean. I've tried the MODE function but that only tells me which invoice number occurs the most.

View 5 Replies View Related

Averages, Mins And Max On Colums With An Insane Amount Of Rows

Apr 22, 2009

I have a problem with a data set that I'm working on. Basically, I've got a data set that was downloaded from a weather station. I need weather data like average temperature, maximum temperature, minimum temperature for each day over a 90 days period. This is simple enough.

The problem, however, is that the weatherstation has been sampling every 5 minutes. In the data sheet, each day is represented by 12*24 rows of data (12 samples per hour times 24 hours). The total dataset across all days is something close to 26000 rows!

Of course, I can spend hours doing all this by manually by average(), min() and max() and then selecting the cells I want it to apply to each time, but the thought of doing so makes me wince. I would have to do it 90 times per column across hundreds of cells each time and I'm after much more than just temperatures.

Is there a less labour intensive way of doing this?

View 12 Replies View Related

Best Way To Parse Colums And Rows W/ Multiple Values Separated By Comma?

Dec 14, 2009

I routinely have do download massive data sets of reporting that is saved as an excel spreadsheet. The three columns of sample data attached are Report Numbers, Report Evaluation Serial Numbers, and Report Evaluator ID.

As seen in the attached spreadsheet, there can be multiple Report Numbers (in same field separated by comma) which have been evaluated by different Evaluators. It is my responsibility to account for the number of Reports that have been evaluated, and many other metrics from like data. The issue I have is when more than one report number is listed in the same field I need to parse the data into its own field for ease of counting (and also maintain the adjacent data). It is not a problem to merely copy the fields and delete the excess numbers, however when dealing with thousands of Reports, and Evaluators this can be very time consuming. In the attached file I have separated .xls into three workbooks to help explain my problem: Initial state of Data; What I need To Parse Out; and The final endstate I require. I hope that I have explained this issue with enough detail. I am sure that the attached file will explain better.

View 5 Replies View Related

Hide Specific Rows In Sheet1 And Hide 5 More In Sheet2?

May 26, 2014

I have a macro in which i can enter the rows i want to hide.

If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].

Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.

View 14 Replies View Related

VBA - Button Command To Auto-Hide Rows With Value HIDE

Jun 9, 2013

Sub Button294_Click()

If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub

View 4 Replies View Related

How To Hide Filter Button

Feb 14, 2012

Its there any way to hide a Filter button? I want to keep all of them on my sheet except for 1. Here's a screen shot:

View 6 Replies View Related

Hide Row Based On Other Cell Value (Filter)

May 5, 2009

i have been able to get most of my VB work on my own (i'm learning). Currently I'm having a bit of difficulty in hiding a row based on a cell value, potentially due to cells being merged.

I'd like to select from a dropdown cell to filter on a row's value. Attached you will find a sample sheet that works named "Working" in it's current state and the one "TEST" that does not filter every other line item.

View 3 Replies View Related

Hide Empty Columns After Filter

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

Hide Columns, Filter & Copy Visable To New Wrkbk

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

Hide Columns, Filter & Copy Visible To New Workbook

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

VBA To Hide / Show Columns Based On Filter And Multiple Criteria

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

Error In Filtering Table - Additional Rows Shown / Rows Hidden When Filter Removed

Mar 4, 2014

I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.

When I start using filters, I run into problems:

Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.

Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)

Step 3: Clear the filter of Unit

Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!

Attachment 301726

View 4 Replies View Related

Hide Blank Rows Based On Values In Other Rows

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

Add Colums Value Together At The End

Jul 25, 2007

Look at the total in Cell A2, B2 and so on..... and multiply by the numbers below - then add them together at the end.

Problem im having is i cant seem to copy and paste the original formula as when i do it for the next row down it wont look at ROW 2, it will look at ROW 3, ROW 4 etc..

View 9 Replies View Related

Returning Last Value In 2 Colums

Nov 11, 2009

I would like to get the last value in column A & B and place them into another sheets cell. The number of rows will always be changing too.. In the attached spreadsheet I want the totals, which is the very last value in the colums.

View 8 Replies View Related

Difference Between Colums In Pivot

Oct 14, 2009

I have an excel sheet with a pivot table with some data about the trades that I have done in the stock market. Before reading the query request you to please have a look at the attachment as I have referred a few data columns.

Coming to the pivot table, in column 'I', I have the 'Total Sum of Trade Value' column which basically adds up the data frome columns E and G (i.e. Sum of trade values under 'Buy' and 'Sell'). I also have the brokerages paid for buying as well as selling and the brokerages are summed up in column J. Basically, what I want to do is to be able to get a difference between different columns within a pivot table. So I want a column which will have 'G-E-J' (Sum of Sell value - Sum of Buy value - Sum of Brokerage). I could click on one of the data fields and after selecting Value filed settings, it shows the summarized by tab.

View 2 Replies View Related

Moving From Colums B To Column A

Jun 6, 2008

how do I move the codes (1CH,1CL,1CP,1EM) from Column B to Colum A under Fee Earner cells where opposite the dates by using a macro?

View 9 Replies View Related

Get Percentage Difference Between Two Colums

Jul 9, 2008

How do i work out the percentage difference between two colums and show the results in a third? I then need to know the average of that third colum

View 9 Replies View Related

Delete Colums Containing Data

Mar 15, 2012

with the following problem : i have a matrix and a list with words and i need to delete every column from the matrix which contains one of the words from my list. I have a VBA script which uses entirecolumn.delete but i need one which doesnt rely on this function. The reason i can't use entirecolumn.delete is that it messes up all of my predefined ranges. here is the script i am using :

Dim c As Range, d As Range
Worksheets("table1").Activate
For Each c In Range("matrix")
For Each d In Range("listofwords")
If c = d Then

[Code]...

View 9 Replies View Related

Retrieving Values From Last Row In Colums

Jan 20, 2009

I'm using this code to input values into the next available row of columns of A and B.


Range("A65536").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Textbox1
Range("B65536").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Textbox2
I have this piece of code right under it:

Range("E65536").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Range("B65536").End(xlUp).Offset(1) - Range("A65536").End(xlUp).Offset(1)

The value being returned is 0.

What I want that last part to do is take the last row of column B and subtract the last row in column A.

View 9 Replies View Related

Selecting Values In Multiple Colums For A Sum

Mar 5, 2009

I am trying to create a spreadsheet to select different values. I have 5 columns with type A values then 3 cols with type B values, a blk col then 1 cols with type B values and 5 cols with type A values then 1 cols with type B values and 2 cols with type A values a blank 1 col with a B value, 2 cols with A values and 2 cols with B values. [ A A A A A B B B Blank B A A A A A B A A Blank B A A B B ]

I need to select 5 lowest values from this set but a minimum of three A values need to be used. I have used the min, small 2, small 3 for the A set then used an IF statement for the fourth value. The formula returns the correct sum for values up to the fourth value. The problem comes on the fifth value. I am attaching the formula I started with.

=(MIN(F3:J3,R3:T3,V3:W3))+(SMALL((F3:J3,R3:T3,V3:W3),2))+(SMALL((F3:J3,R3:T3,V3:W3),3))+(IF((MIN(K3: M3,U3)<SMALL((F3:J3,R3:T3,V3:W3),4)),(MIN(K3:M3,U3)),(SMALL((F3:J3,R3:T3,V3:W3),4))))+(IF(SMALL((K3: M3,U3),2)<SMALL((F3:J3,R3:T3,V3:W3),5),SMALL((K3:M3,U3),2),(SMALL((F3:J3,R3:T3,V3:W3),5))))

View 4 Replies View Related

Macro To Clear Colums In Range

Jun 19, 2009

I am trying to create a marco that clears the columns of a named range if the sum of the cells in the range is zero.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved