Free Microsoft Excel 2013 Quick Reference

subtotal only visible columns

hi could anyone please help with an alternative to function subtotal 109 which does not work on columns. I have attached a sample with column z hidden.


Post your answer or comment

comments powered by Disqus
hi, i having problem in Subtotal that visible column only.

need help



but after i hide the COLUMN B. Result in COLUMN D does not change

here's my simple formula.

Thanks and regards to all.

I would like to autofit only visible columns(column width to longest cell) from a filtered list.

Want to copy and paste data from one worksheet to another. The
worksheet that we will paste TO has hidden columns. The worksheet we
are pasting FROM does not have hidden columns. HOw can we paste the
data to ONLY the visible columns in the recieiving worksheet?

I hope I explained this so it makes sense. Many thanks.


I've got a question for you guys.

I've got two spreadsheets with data, and I want to align the data from spreadsheet A with spreadsheet B. So in other words, I want to copy what is on sheet A to sheet B, so I can align them. However, sheet B has hidden columns. So when I copy from sheet A to sheet B some of the columns from sheet A are populating the hidden columns instead of the visible columns, and that is causing an issue.


Spread shet A has:


When I go to paste to spread sheet B, I get this.


What I'd like to get is this:


In the process of writing this, I realize that there are several work arounds, but if an answer exists, I'd still like to know for my personal gain.

So thanks for any help!

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?

 AllCells = ActiveCell.CurrentRegion 
With AllCells 
    .Font.Size = 6 
    .Font.Bold = False 
    .WrapText = True 
    .EntireRow.AutoFit = True 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a large database of 46489 posts. I have used subtotals to add them up. In the past have I used Edit-Got to-special-visible cells only, to copy the subtotals to another sheet, but this time I get "too complicated area" from XL. Do anyone know of a workaround apart from the obvious move to Access. I use W2k, XL2K and have 512 mb ram.

TIA Mats

Hi All,

If possible, could someone help with the 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.

Sub Cell_Resize()
With Cells.SpecialCells(xlCellTypeVisible)
    .WrapText = True
End With
End Sub


I need to subtotal two separate columns at the same time in Excel. I have
item numbers in one column and the corresponding quantity of each item in
another column. Each item has many different rows. Using the Subtotals
command under Data, I am only able to subtotal by the item number and then I
have to go in manually and sum the quantities of each subtotaled item. Is
there a way using Subtotals or something else to subtotal by item number and
then sum up the quantity column related to each item number at the same time?

Thank you,


I am stuck trying to find a solution to the following problem. What is clear is that it should not be written in VBA because I need the result to appear in every cell in column AW.

To summarise;

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.

I would be very grateful for any help because I'm really stuck on this one.



Hi Guys

I'm Quite New To excel vba so need a little bit help regarding one of my problem

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

n had used


and 2nd is 

speical cell property

Both r not working
Plz if u could help me with a macro to do this i'd be really gr8full

Ravinder S

How do you copy only subtotal values to a new column or spreadsheet? I collapsed the rows to show subtotals only.



I have filtered a worksheet and want to only total the data in the columns that are visable, is this possible?

Another annoying problem, this one slightly less vexing that the last, but
preventing productivity nonetheless! I have a two workbooks. One
"Control.xls" has all the user interface and coding. The other "Travel.xls"
has all the data. Near the end of my process I filter the dataset to show
only what I want to keep, which is:

Only Visible Cells of Active Area in Columns A:O.

The number of Rows varies and is the part driving me crazy.

I've tried to accomplish this by copying my target range from one sheet to
another, and then moving it back after deleting the data from the first
sheet, but I can't seem to get the data to move from one sheet to another
without setting a fixed range. Any ideas on how to make this work?

Sheets(1).Range("A1:O" & endRow).SpecialCells(xlCellTypeVisible).Copy
Operation:=xlNone, SkipBlanks:=False, Transpose:=False)

How to Use a VBA Macro to Sum Only Visible Cells

For more information about creating custom functions in Microsoft Excel 97, from the Visual Basic Editor, click the Office Assistant, type function statement , click Search, and ...

I have an array of say 5000 rows and around 16 columns. All the cells are non-blank. I have certain conditions. I want to display from these rows select ones BUT only select columns in another worksheet. Currently I am served well, by the following command, which infortunately displays all the 16 columns, i.e. entire row gets displayed meeting the conditions what I specify. What I need is to display say just 3 or four select columns. The command I am using is
Please guide me

A N Sridhar

How can I copy only visible worksheets to a new workbook using VBA


Hope you can help me how to code the following
How to import a csv file where it should only import only columns with a specific text (header) as it can sometimes differs from columns

Eg in cell

A1 Client Name
B1 Contracted
C1 Address
D1 Sales
Lets say I want only the columns Client Name, Sales. Etc imported to save speed and space

Can anyone help me pls?


I have a query in Access that returns multiple rows. I need to export the data to Excel. But I only need one column of data.

Normally I'd use the CopyFromRecorset method, but this copies ALL the data. What's the easiest way to copy only one column?

Hello All,

I've been supplied with a function that can help me through a friend.

    Dim cl As Range, UniqueValues As New Collection 
    InputRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True 
    For Each cl In InputRange 
        If Not cl.Entirerow.Hidden Then 
            UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
        Next cl 
        On Error Goto 0 
        CountUniqueValues = UniqueValues.Count 
    End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Right now, the function does not work but the layout is correct. I was wondering if any expert programmers can reorganize the layout for me? It's supposed to create an array of all the unique values. Any duplicates are not added to this array and at the end, a count of this array is provided. It does not work after I added the If statement there but I need that condition as I want to count the number of unique entries within a filter. Hence, if there are hidden lines, this function would calculate the total number of unique entries only visible at that time. Perhaps this method of creating an array is not feasible; are there any suggestions? I think the approach is somewhat working but I need an alternative to simply making another array. Thanks alot.

I have a spreadsheet of approximately 40,000 lines of detail. When I subtotal ,I get about 1000 subtotals. I need to copy the subtotals only to another worsheet without all the details so that I can have a new worksheet with only the 1000 subtotals.

anyone know of a macro to copy only visible cells in a range?

I have a MASTER workbook that contains a MASTER tab and a series of subordinate tabs containing select columns from the MASTER as well as pivot charts/tables. The MASTER tab reads 5 other workbook files and displays data accordingly in 17 columns. The subordinate tabs, depending on the tab, may only display column 1, 3, 7, 9. I am simply copying/pasting the select columns from the MASTER to the subordinate (time consuming). However, when this is performed, the data is pasting as static text and the "linkage" to the 5 other workbook files is not maintained. Why? Everytime a row is added to the MASTER, I have to repaste the entire MASTER and delete the rows I do not need just to maintain the "linkage".

Is there a way to have the subordinate worksheets read the MASTER tab and only display the select columns it needs without the annoying manual process of copy/paste I have been performing? I welcome any and all suggestions. (If it matters, I am using Excel 2002) Dkershner


is it possible to filter only specific columns in excel 2003?

for example I have autofilter on all of my columns A-Z.

what if i just want to have a drop down filter for A-D and then G-K and then N-P, etc. etc.

Any help is much appreciated. From the gurus I've talked to here at work it can't be done. I can only select a few columns in sequence and have the filters there or it breaks the merge apparently of columns. So it's either all the columns with the autofilter to the left/right only but not a few here and then a few over there if that make any sense. Thanks!!

Can someone please help me with some code.

I need to build a macro, which will delete all rows with a specific value in column A, only if column B does not equal a different specific value. I can't seem to find any relevant posts. thank you very much.


No luck finding an answer? You could always try Google.