Counting Filtered data cells, copy and paste values from another workbook

Maniac

New Member
Joined
Apr 7, 2014
Messages
6
I have a filtered worksheet -WB1 (filtered by Column "B"). I want to count the number of cells or rows in column "B"(Only the filtered ones) of WB1. Copy out that exact number of cells from another workbook(WB2) from the bottom moving up(Column "A") and paste it into WB1 column "I".


I Hope my explanation is ok.


WB1 - Count Filtered Cell/Row with reference to Column "B"
WB2 - copy Cell count bottom to top of Column "A"
WB1 - Paste into Column "I"

Any help would be good.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Maniac

New Member
Joined
Apr 7, 2014
Messages
6
This is the quote that I have that counts the cells correctly. but only copying the last cell of Column A and pasting it in Column I. I want it to copy the count indicated in Column from bottom to Top and paste it in column I.

There is another complication to this. Even if it selects from Bottom to Top, I want it to paste in the exact same way.

e.g Copy A39:A31 Paste it into filtered cells as A31:A39 (Top to bottom)

Lastrow = .Offset(1, 1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Count
wsSource.Cells(Lastrow, "A").End(xlDown).Copy
wsTarget.Range("I" & Lastrow).End(xlUp).Offset(1).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
 
Last edited:

BlueHornet

Active Member
Joined
Apr 13, 2012
Messages
338
Welcome to the board!

Use the SUBTOTAL function and the argument for "count". When you use that function it only acts on "visible" rows or columns (depending on whether you're subtotaling row-wise or column-wise.

For example:
Sheet1
ABC
1DateStringNumber
41-MarHarry3
51-AprTom4
61-May****5
7
83312

<tbody>
</tbody>
Excel 2010

Worksheet Formulas
CellFormula
A8=SUBTOTAL( 2, A2:A7)
B8=SUBTOTAL( 3, B2:B7)
C8=SUBTOTAL( 9, C2:C7)

<tbody>
</tbody>

<tbody>
</tbody>

You can't see it because of the filters, but I have other dates, other string values and other numbers in the hidden cells. They don't figure into the results.

It's often useful to use this in large data sets to show the total count of rows (or grand total values) in one cell, and the filtered values or counts adjacent, so that reviewers can estimate (or see in additional calculated values) what percent of the total they're seeing.
 
Last edited:

Maniac

New Member
Joined
Apr 7, 2014
Messages
6
Tahnk you for your replies.

However.. I cant seem to get this into my code.

The problem is only WB1 is filtered and not workbook 2.

with the code, it selects the last cell of column A (partially correct) but it is supposed to select the count as the selection depends on the cell count from WB1 - bottom to top. - it manages to read the count correctly from the code below.

Lastrow = .Offset(1, 1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Count

Then it only selects the last cell but I want it to select the cells according to the count(this workbook is not filtered)
wb2.Cells(Lastrow, "A").End(xlDown).Copy

Then paste it to column I of WB1 which is filtered. So I need to paste in only into the visible cells.

wsTarget.Range("I" & Lastrow).End(xlUp).Offset(1).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False

The Subtotal seems to do the trick but unfortunately I'm a novice with VBA and I don't know where to input this.
 

Maniac

New Member
Joined
Apr 7, 2014
Messages
6
lngRowMax = Range("A65536").End(xlUp).Row
Lastrow = .Offset(1, 1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Count

Ok, my code above selects the last cell in Column A and I want ti to select the count cells as of Lastrow

Eg: lngRowMax = A21
Lastrow = 9

So it should copy A21 all the way up by 9 cells.

A21:A13/A13:A21.

Anyone who could help me, please?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,471
Messages
5,636,512
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top