Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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

Sponsored Links:

Related Forum Messages:
Resize Visible Rows Based Only On Visible Columns Text
Need to correct code to resize all visible rows on a sheet based only on the text in the visible columns. I have tried the below code but when it resizes it is using the largest amount of text in the rows including that in the hidden columns.

View Replies!   View Related
Write Visible Worksheet Rows/Columns To Text File
I am trying to write the contents of a worksheet to a notepad file. Also, the worksheet has some hidden columns.

View Replies!   View Related
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:

View Replies!   View Related
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.

View Replies!   View Related
Filtering Columns
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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).

View Replies!   View Related
Filtering Two Columns And Setting Specific Value In Third
I have requirement wherein, I have to put filter conditions on two separate columns in the same sheet & putting hard coded value eg. "XYZ" in third column for this selected range. I am using macro autofilter but its giving me errors for multiple filters.

View Replies!   View Related
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.

View Replies!   View Related
Hidding Rows With Checkboxes And Coping Visible Rows To Another Sheet
1. I have to hide rows using checkboxes
2. I have to copy visible rows (not marked ones) to another sheet in the same workbook. So far i have managet to creat this code for hiding/sowing rows:

View Replies!   View Related
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.

View Replies!   View Related
Filtering Empty Rows
i have a table with game results like: ...

View Replies!   View Related
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
With AllCells
.Font.Size = 6
.Font.Bold = False
.WrapText = True
.EntireRow.AutoFit = True
End With

View Replies!   View Related
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.


View Replies!   View Related
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.

Sub prepmetrics()
' 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?

Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

' 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"
Selection.Delete Shift:=xlUp

Application.ScreenUpdating = True

End Sub

View Replies!   View Related
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".

View Replies!   View Related
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.

View Replies!   View Related
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)
With Me.Cells
End With
End Sub

Found here: Automatically Adjust Row Height & Column Width

View Replies!   View Related
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.

For eg:

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

View Replies!   View Related
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.

View Replies!   View Related
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 & 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 Replies!   View Related
How To Copy Only Visible Rows
I have a spreadsheet that has hidden rows, I want to somehow either delete the unvisible rows or to be able to copy the visible rows to another sheet?

View Replies!   View Related
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.

View Replies!   View Related
Counting Only The Visible Rows
How can I count the no. of rows of a selection which has some hidden rows...I want to count only the visible rows

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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?

View Replies!   View Related
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.

View Replies!   View Related
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.

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


View Replies!   View Related
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?

for example....


row header a | row header b
line 1
line 2
line 3

after auto filter:

row header a | row header b
line 2

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)

View Replies!   View Related
Select The Visible Cells And Count Down 50 Rows In Column
I would like to select the visible cells, and then count down 50 rows in column A - is this possible?

View Replies!   View Related
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.

View Replies!   View Related
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)

With ActiveSheet
Set rnData = .UsedRange
With rnData
. AutoFilter Field:=1, Criteria1:="5"
.Select 'demonstrate that the rnData range is valid
lcount = .SpecialCells(xlCellTypeVisible).Rows.Count
End With
End With

View Replies!   View Related
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.

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

View Replies!   View Related
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.

Sub asdf()
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))

View Replies!   View Related
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 ""that contains only basic data on Sheet1 and Sheet5 as those are the only sheets relevant.**

View Replies!   View Related
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%.

View Replies!   View Related
Sumproduct- To Get The Total Number Of Columns That Have A Specific Match-up
I've got two columns of numbers, such as:

(Col A) (Col B)
Cycle Bin
1 - - 3
1 - - 7
1 - - 7
1 - - 2
1 - - 5
2 - - 7
2 - - 9
2 - - 6
3 - - 7
3 - - 2
4 - - 2
4 - - 8
4 - - 2
4 - - 8
4 - - 5

I'm trying to get the total number of columns that have a specific match-up, for instance, how many cells are in Cycle 1 with Bin #7? ....

View Replies!   View Related
Add Separate Columns Together To Get A Total Count Of A Specific Criteria
I am trying to add separate columns together to get a total count of a specific criteria. The formula I am using is:

View Replies!   View Related
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.

View Replies!   View Related
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.

******** ******************** ************************************************************************>Microsoft Excel - RowBasedTotal.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB16C16=ABCD1NameAmount1Amount2 2Ashu10050 3Basu200100 4Natu300150 5Garu400200 6Ashu500250 7Basu600300 8Natu700350 9Garu800400 10Aayur900450 11Mayur1000500 12Basu1100550 13Gastu1200600 14Natu1300650 15Garu1400700 16Total105005250 Input [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** ******************** ......................

View Replies!   View Related
Delete Rows Until, Total Found In Column
Is there a macro that will begin in cell A2 and delete rows until the contents “Total” is found in a random row in Column A?

View Replies!   View Related
Forumula To Total Items In Multiple Rows
I have ~500 rows of data in columns A, B, C that is as follows, for example:.............

I am having trouble with coming up with a formula that will add up the total items shipped for each item. For example, Apples = 62 items shipped.

View Replies!   View Related
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.

Sub AddColumn()
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"
End Sub

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?

View Replies!   View Related
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.

View Replies!   View Related
To Find A Total Number Of Rows To A Blank Cell
I need to find the total number of rows down to the next blank cell (and then perform a function based on that number).

I'm using:

Situation: I have a raw data import - each record is anywhere from 2 to 9 rows, and I need to move each row in that group into a column.

I would like to use something like:

totalRows = Application.WorksheetFunctions.CountA(Range("A1, xlDown"))
If totalRows = 4 Then
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Offset(-1, 1).Range("A1").Select

View Replies!   View Related
Copyright © 2005-08, All rights reserved