PDA

View Full Version : Getting a total copied set of rows in VBA and storing it in a variable



Growler
09-14-2011, 12:06 PM
Hello,

I have a fairly simple syntax question:

I'm trying to copy and paste n rows from one excel file to another. In addition, I'd like to store the total filtered copied rows into a variable. Can someone help me accomplish this?

For example:

'1) Activate CSV file, Apply Filter to Column B (Page Title) & uncheck "blanks" ("<>") filter
Windows("Test_Origin.xlsm").Activate
ActiveSheet.Range("$A$1:$J$206").AutoFilter Field:=2, Criteria1:="<>"

'2) Copy Filtered Lines with data (Excluding Row 1)
Range("B2:F189").Select
Selection.Copy
copiedRowTotal = Selection.Rows.Count <-- This doesn't give me the FILTERED rows

Thanks

mancubus
09-14-2011, 03:23 PM
Sub CountVisibleRows()

Dim filterRange As Range
Dim copiedRowTotal As Long

Set filterRange = ActiveSheet.AutoFilter.Range

copiedRowTotal = filterRange.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
'-1 to exclude header row

MsgBox copiedRowTotal
'to display the copiedRowTotal value in message box

End Sub