How To Total Only Visible Columns Or Rows After Filtering
I have filtered a worksheet and want to only total the data in the columns that are visable, is this possible?
View Complete Thread with Replies
Related Forum Messages:
Determine Total Column Width Of Visible Cells
I am trying to resize a picture on a worksheet to fit a dynamic measurement(the size of the page is different depending on the contents in cells).
I am doing this all in VBA.
so what I have come up with is that I need to
1) determine the total column width of only the non-hidden(visible) cells.
2) resize my picture to those measurements, so I can get an exact fit, and it doesn't create a new page break, by going over.
I have found some code on forums, and am thinking of something like this as a solution(although I don't know how to complete it):
For summing column widths:
Running Total Of Visible Values In A Filtered Table (2007)
I have a data table with all my data in it. I want to be able to filter the data, and have a column automatically calculate a running (or aggregate) sum of the filtered (visible) information in a particular column of the table.
If you glance at the attached sample spreadsheet (in 2nd post!), you will quickly see what I mean. I am searching for a formula for column L that will caculate a running sum for column K. In column T, you can see the results that I'm looking to reproduce in column L. Each cell in column T simply adds the K column value for that row to the previous value in column T (the cell above). However -- to my knowledge, and as in this case -- the formula for column T can only be copied down the column AFTER the table has been filtered.
I have an excel with the following information
A1 - Is a dropdown which will have names of the systems (EAI,Mediation,CDF
Row2 will be header which will have following data
B2 - Project Id
C2 - System ID
D2 - Project name
E2 - Project Phase
F2 - Status
from g2 to GN2 there are about 60 resources names entered
From C3 to F3 the data wil be entered and appropriately a chk mark will be
made to the corresponding resource wroking on that project
Say i have about 10 systems provided as dropdown for entering data in A1
(Ex: EAI, MEdiation, .....)
There are resources working for each system
when i apply filtering on a system name i shoud be able
to show only those resources working for that system and all others should be
Also under the "system" dropdown i will have an option "All", when "All"
option is selected i should be able to see all the data for all the systems.
EX: if i take system "EAI" and if the resources working for eai are there in
AO2 to AX2 then i want the resources from AX2 onwards and before AO2
not to be shown.
If i take a system "Mediation" and if its resources are from Z2 to AI2
then i want resources before Z2 and after AI2 notto be shown.
If "All" option is chosen then i should be able to see the whole sheet
with all the data for all the systems and all resources.
Filtering Multiple Columns
I am trying to filter three columns of data to a given range without having to using auto filter on each column. I would like to have a Macro or Control Button or something easier than picking each set of data each time I want to filter.
Attached is a sample file.
I am not well versed in Excel and this is my first post so please bear with me in this process.
Filtering Multiple Columns At Once
a formula in excel. here's what i need to do:
column A has the names of deffirent drivers (ex; john, peter, james)
column B has start time (9:00, 10:04, 11:08)
column C end time (9:14, 10:16, 11:23)
i need a formula that will take all the "john" from column A, do column c - column b for the time it took and then do an average of all of john's attemps.
Filtering Data In 2 Seperate Columns
I'm trying to put together a sheet to track football results. For simplicity sake, the sheet looks like this...
Home ; Away ; For ; Against ;
a ; b ; 2 ; 0
c ; d ; 1 ; 1
b ; c ; 1 ; 3
d ; a ; 2 ; 4
a ; c ; 1 ; 2
By using Autofilter on the home or away columns, i can obviously filter it in order that I can see all A's home results or all of A's away results, but by doing this you can't just filter the sheet in order to see the results of every game A plays in. I have a feeling that I need to do something with Advanced filter but have been playing around with this and not getting any joy. Can anyone advise on what I need to do?
Ultimately, my end goal would be to be able to create a drop down list where i would select the team from a list of all teams and this would automatically filter the sheet to their results only (and then I could use an autofilter to look at just homes or aways).
Filtering Data Using Two Separate Columns
I have text in column A and text in column B. I would like to create column C and place the number 1 in each row whenever columns A and B contain the text I am looking for. I could then use C to filter all the 1's with ease (by way of macro or the autofilter). In my problem, column A contains the following text per cell:
Column B contains:
I need to filter based on column A having the letter "A", or column B having the word "MATT". Columns A and B do not have to contain both "A" and "MATT", respectively, in order for column C to have a 1.
FIltering Non Blank Rows
I have a very long excell sheet and I want to be able to have two buttons off to the side that allows me to filter and unfilter my page. What I want to accomplish is if the cell in column "A" is not populated then I do not want to see that entire row when I filter.
AutoFit Visible Columns Only
I have a worksheet that has several hidden columns and wish to have the EntireRow.Autofit apply to all rows in the current region based on visible columns. After using the WrapText method on all visible cells, one of the hidden columns with a lot of characters causes the entire row to be much larger than desired. Is there a way to use EntireRow.Autofit to only expand the height of each row in the current region as necessary based only on the visible cell contents in that row?
Set AllCells = ActiveCell.CurrentRegion
.Font.Size = 6
.Font.Bold = False
.WrapText = True
.EntireRow.AutoFit = True
Filtering, Wrong Rows Removed
I have a module that imports data from a text file (120000 rows), filters and removes some unneeded (85000)rows and then puts the data in a designated workbook.
Problem: when filtering by some criteria it for some reason removes a row off the final file for each criteria. I have a total of 23 filtering criteria. I have attached a file for exemplification. You can run the module and import the file, it will be without first 23 rows, even if non of the deleted rows contained the filter criteria.
Filtering Rows With Particular Values In A Column
I have been trying to transpose a piece of code that was put on this forum back in 2005 into my own Workbook but have been unsuccessful. The original piece of code filtered a column for unique entries and then filtered the main sheet for each unique entry and cut and paste each row that the unique value occurred in and in a new worksheet. A new worksheet would be created for each unique filter.
So what I am trying to do is look up a list of values that i have in a list on the worksheet. Then for each entry in my list, create a new page and cut and pasted the filtered main worksheet into that page.
Finally return to the main worksheet and then remove all filters.
I have attached the original file that was submitted so you can look at how it works and I have posted a sample my worksheet (with some things disabled to reduce file size but hopefully you will get the layout. However I don't currently have that sample on this computer. It is posted on another thread under the title "Selecting rows with specific values in a column".
Read Only Visible Columns From A Range
I do hav a sheet in which i do hav some column;s n data
in work type few rows are hidden which i don't need what i need is i want to populate only comman metrics in other range named metrics i had build a macro for this and that's working fine but the problem is it also reads metrics from hidden rows too. i had tried a loop in which i had placed as hidden column property condition.
Filtering And Deleting Rows With VBA Code
I've got a row of data with a column showing some sales including amounts with 0. Any row with 0 I want gone, so I want the macro to filter the list, delete all the rows with 0 in that column, and then display what's left, all the rows with some number other than 0 in that column.
The code below should filter the 0 rows, delete those rows, and then display what's left. I don't want the heading row deleted, but everything below it that is filtered. What my code below is doing now is not deleting everything it should. How can I fix it.
' prepmetrics Macro
' for use with monthly report - don't forget the new coversheet
Application.ScreenUpdating = False
Selection.AutoFilter Field:=14, Criteria1:="0"
' this is the challenge here. How do I tell Excel to select, relatively, whatever
' rows are now showing as a result of the filter?
' there may be a number of different criteria in different columns, so
' we rinse and repeat as many times as necessary but, again, each filter/delete
' task should delete all rows filtered
Selection.AutoFilter Field:=16, Criteria1:=">7500"
Application.ScreenUpdating = True
Make Columns Visible/invisible As Per The Conditions
I am trying to create a daily task activity sheet. I would like to know, how to make the columns visible and invisible as per the selection from previous column.
Column A has a list which has values 'TC creation' , 'TC modification', 'System Test' etc.
Upon selecting TC creation, i need to make a column visible ie 'total tcs created'.
Upon selecting 'TC modification' i would have to make the previous column invisible and make a new column visible for entering no of tcs modified.
Upon selecting 'System test' the other columns should go invisible and make other 3 columns visible for no of tcs executed, no of tcs passed, no of tcs failed etc...
Automatically Auto-Fit Visible Columns Only
In the code below, I found out how to make the column widths automatically adjust on a worksheet based on the text entered. This code works great but it also reveals the hidden columns on the worksheet. Can i prevent it from doing that?
Private Sub Worksheet_Change(ByVal Target As Range)
Found here: Automatically Adjust Row Height & Column Width
Hpagebreaks On Visible Rows
to put hpagebreaks when one or more checkboxes are checked. For example when CheckBoxChange and CheckBoxDisco are checked, hpagebreaks are put below the Change data and Disco data.
The checkboxes also hide rows of data when checked.
Sum Only Visible Rows
I have this sheet:
2 1 1
3 2 2
4 3 3
5 2 4
6 1 5
7 2 6
8 3 7
9 1 8
10 1 9
SUM(B2:B10) gives 45
Now I add a filter so all the rows with A = 1 will be hidden. That gives me:
3 2 2
4 3 3
5 2 4
7 2 6
8 3 7
But SUM(B2:B10) still gives 45. But it should be 22.
Counting Visible Rows
I have two macros that hide or unhide rows. Now I need a way to count only the non-hidden rows. The total needs to be visible in each visible cell of a single column (presently column AW).
Example - if rows 2 and 3 are hidden using the "Hide Row" macro, the following would happen;
Row 1 is visible, so it is numbered "1" in AW1.
Row 2 is not visible, so it either has no number or freezes at "1" in AW2.
Row 3 is not visible, so it also has no number or freezes at "1" in cell AW3.
Row 4 is visible, so it is numbered "2" in AW4.
And so on.
It doesn't really matter what is in the AW cell in hidden rows because I am not totalling rows. What I am doing is using it to define a print area. If a page has to be 70 rows long, another macro uses column AW to work out where to put page breaks (after row 70, 140, 210 and so on). If twenty rows were hidden on page 1, without the above solution, the printed page would only be 50 rows long. A solution to the above problem will always make sure only visible rows are counted when calculating where to put page breaks.
I am using Excel 2000.
Hide Columns, Filter & Copy Visible To New Workbook
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"
Userform Autofilter Visible Rows
I have a Userform that allows you to step through the the spread sheet that works with Previous and Next buttons. This works fine.
When I use an autofilter where the criteria reduces the number of rows the Userform shows the hidden rows as well as the filtered rows. how can I make it just show the visible rows.
Count Numbers In Visible Rows Only
If you look at the attached file in Row 41, Column H, the count function is not working properly... it is mis-counting, the numbers that appear. It does not work either in F41 and G41. It seems to be having a problem reading the if function I created. Does anyone know how to correct this, or change the if function formula so it doesn't have these issues.
Rows Not Visible: Enumeration Skips
I have a spreadsheet that shows is a break in the enumeration of the rows. To elaborate, rows are enumerated from 1 to 15, then after row 15 is row enumerated as 25 and continues to be enumerated as expected. In other words:
1, 2, 3, 4, ..., 14, 15, 25, 26, 27, ...
How can I get back rows 16 to 24 be visable again?
Rows On Worksheet Are Not Visible Or Sequential
This is so simple that I cannot even do a search for the answer. My worksheet has a horizontal and vertical cross. After row 16 for example, what is seen on the screen is row 24 for example. I did look at the instructions for "hidden" but I don't think this is a "hidden" issue. I don't want the rows to "jump" numbers, but want them listed sequentially down the page.
Count Visible Rows After AutoFilter
Is there a simple way to count unhidden rows? I saw Counting number of unhidden rows =SUBTOTAL(103,D11:D7180) and, frankly, I'm wondering if there is a way to do it without a formula. I don't need the count in a cell, per se, just a quick count of the unhidden rows of a worksheet for usage elsewhere.
Select Cell Content From Visible Rows.
I have a worksheet whereby many of the rows are hidden. These rows were initialy hidden by means of scipts ( I have numerous scripts to hide data under various criteria) eg: Scripts which hide rows based on cell count, cell color, cell data information etc...
Now that I am able to veiw only the information that i want to see.. is it possible to create a script which selects cells only from rows which are visible? I have created a Named range begining from E:12 to G:500 called "Select_EFG"
Uing the example below, how could I select the cell data from the visible rows (12, 30, 34, 35, 50 etc... up to row 500.) from this Named Range (columns E:12, G:500) .... and paste this information in Sheet 2 Row5 columnC
Pass Number Of Visible Filtered Rows
what i need to do is store the number of rows visible on the data sheet (after a data filter is applied) into a variable so i can do a calculation with said data. Is there such a command?
row header a | row header b
after auto filter:
row header a | row header b
code stores number of rows visible into a variable....
vVariable = rows visible... in this case 2 (NOT 4... which is the total rows including hidden)
Return Count Of Visible Rows After Auto Filter
I have tried and tried to get the VBA code working that will tell me the number of visible rows in an autofiltered set of data, but the result I seem to be getting is always "1". Below is the most simple form of the code that I am using (it is based on previous posts and tutorials on this site). (I have also attached a workbook with sample data and the code)
Set rnData = .UsedRange
. AutoFilter Field:=1, Criteria1:="5"
.Select 'demonstrate that the rnData range is valid
lcount = .SpecialCells(xlCellTypeVisible).Rows.Count
Delete Blank Rows :: That Don't Contain Visible Numbers Or Letters
I'm trying to clean up several long worksheets by removing rows that don't contain a visable number or letter.
I want to remove all rows that only contain either empty cells, formulas that evaluate to a empty text string (""), cells that contain error values, or cells that contain zeros, or any combination of the previous.
I've stumbled onto the following, but it does not work.
Delete Visible Rows After Auto-Filter & Exclude Headings
I am asking if it is possible to delete filtered rows? With code.
What I mean is after setting a filter, then deleting all shown rows except row 1, (Titles)
I did a search but nobody seams to have asked this yet, so recorded it, but that did not seam to enlighten me much either.
Or is this the wrong approach, should I delete using a loop, using the filtered criteria as to say delete row, or move on to the next row?, this would be far more time consuming as when all can be deleted at once if possible of corse.
Stacked Columns With A Total
I have a stacked column graph showing data for 5 categories for 4 years (4 columns, 5 categories each).
I would like to display a Total above each column. I was able to do this on a single chart by just adding a text box and manually adding the total amounts. However, I would like this to be updated dynamically, as the data will be changed frequently.
Is it possible to:
(a) Have the total display as a data lable, or
(b) Have a text box containing a cell reference
Delete Columns That Total To Zero
I need code that will check each column from D:P in the " Total" row for a value of Zero. If it finds a zero then delete entire column. This will be the final procedure of a macro that does other formatting things so the number of rows will differ causing the "Total" row to be dynamic. I have the following code that will find the word total, but I don't know how to set the range to offset 1 col each time checking for zero and deleting if true. The word "Total" is merged between columns A:B. see example sheet.
Dim c As Variant
Dim Rng As Range
'search the sheet for "Total"
Set c = Cells.Find("Total", lookat:=xlWhole)
'if it is found
If Not c Is Nothing Then
Set Rng = Range(c, c.Offset(0, 3))
Total Of Two Columns Containing Numbers And Words
I am attempting to create a chart from data generated outside of Excel 2003 SP2, that has been Copied and Pasted into Excel. There is no metadata associated with this data. I have created a list with the data once it has been pasted into Excel 2003 SP2.
The Workbook has five (5) worksheets, all with the default names (Sheet1, Sheet2, etc.). I paste the data into Sheet1 and reference this from the other worksheets. Sheet2 shows the charts for all the data. Sheet3, Sheet4, and Sheet5 are each for different regions, North, South and West respectively. I would like to enter the formula into Cell: Sheet5!M5 so it auto populates with the appropriate and correct information. Sheet1 information is as follows;
Column B has a header of "CreateDate" and is in the format of; 4-Jun-07
Column O has a header of "Business Unit" and has three (3) possible answers, North, South or West. **I have attached a screen shot of the two colums used named, "Columns.Used.png". I have hidden the columns in between as they contain confidential information.**
I would like a formula that gives a total of one if both criteria are met. The criteria is as follows;
IF Column B on Sheet1 equals Cell L5 on Sheet5 AND Column O on Sheet1 equals West THEN the total should appear. **I have created and attached a sample spreadsheet "Sample.Data.zip"that contains only basic data on Sheet1 and Sheet5 as those are the only sheets relevant.**
Total Number Of Unique Matches In Two Columns
Column A = numerical IDs, multiple entries, may have duplicates that should only be counted once in comparison
Column B = numerical IDs, multiple entries, may have duplicates that should only be counted once in comparison
Need: 1 cell that compares all of the A range to all of the B range and returns a a percentage of repeats. So, if there are 50 entries in A, and only 40 are unique, and B includes 10 repeats (unique, not multiple), then the returned value would be 25%.
Group Rows And Total
I have a sheet named “Input” where the name of the persons appear more than once as the same is made by consolidating monthly sheets.
Now I want to make summary of each persons to a new sheet named “Output”.
In the new sheet, the name should appear once.
The name should summarize the total of all the columns corresponding to the name.
For eg name Ashu may appear more than once in the Input sheet but in the Output sheet this name should appear once and shall summarizes all the amount for all the rows where Ashu has appeared and so on.
******** ******************** ......................
Add The First Two Rows And Get A Total
I have two columns, one with part numbers, the other with quantities. My system breaks the inventory down into locations, which doesn't allow me to see the total number of parts allocated without adding up the individual locations. I was wondering if there was an excel formula or macro that could do this for me.
For example, my data looks like this:
Part Number QTY
As you can see, if I want to find out the total number of part AAA that is allocated in the system, I need to add the first two rows, to get a total of 7. Now, imagine a spreadsheet with roughly 3,000 rows, and you can see why I would like an automated process for this.
Total A Column With Unknown Number Of Rows
In the code below, a formula is placed in column F to compute the total of that column. I first find the number of rows and place the formula in the cell below it. I'm dividing the sum by 2 since there are subtotals in the column.
Dim NumRows As Long
NumRows = Range("A65536").End(xlUp).Row 'get the row count
NumRows = NumRows + 1
Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:F308) / 2"
The problem with the code is that I don't really know that the last row in the column is F308. I need to replace that part with a variable. It will be something like this (which I know is incorrect):
Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:NumRows) / 2"
How would I use a variable in the SUM function in place of the cell names? Should I even be placing the formula in a cell, or should I compute the sum in the macro and place the value in the cell?
Turning 64 Rows And 18 Columns Into One Row With 1152 Columns
I have individual data files (one for each participant). Each file has 64 rows (one for each trial in an experiment) and 18 columns (one for each variable). I would like to create a master data file in which each participant is represented in a single row. I will then have 1152 columns (18 columns for the variables, repeating 64 times so that each trial is represented).
In other words, my individual data files look something like this:
Participant 1 (title of data file)
1 55 43 65
2 54 43 56
3 33 23 56
and I want to compile all the data into a single file that looks like this:
1 55 43 65 54 43
2 . . .
I know how to record macros and use IF, THEN, AND, & SUMIF statements creatively, but that's about it. I was hoping to record a macro that I could use on each individual data file to turn it into a single row, and then paste each row into the master file.
Total Data- Having A VB Code To Delete Empty Rows
The spreadsheet I am working in has $ which I need to total. The information, or rows, can vary from day to day so there is no static place to have a cell with an @sum. Management wants me to add a pretty line that says
'Total' in one cell and the $ next to it. I have about 30 sheets with columns to total. Yuck, takes forever.
How do all of you total if you don't know the last row? At the top?
I thought it might work to put an @Sum in the last row of the spreadsheet, in the column
I need to total, and having a VB code to delete empty rows. That would be automatic.