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

[Maniac]

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.

 

[Maniac]

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

 

[BlueHornet]

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

	A	B	C
1	Date	String	Number
4	1-Mar	Harry	3
5	1-Apr	Tom	4
6	1-May	****	5
7
8	3	3	12

<tbody>
</tbody>

Excel 2010

Worksheet Formulas Cell Formula 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.

 

[mfexcel]

SUBTOTAL would be a solution for you.
Check it out.
SUM vs. SUBTOTAL | wmfexcel

Hope it gives you some ideas.

 

[Maniac]

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]

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?