Autofilter and Count Row to insert on another worksheet

[ElianaMG]

I am working on two worksheet in the same workbook, wsSheet1 and wsSheet2, and I need to do an autofilter (unique = true) in wsSheet1, count the lines and insert one line for each value on wsSheet2, but I just can't get it done! So far my code is not filtering only cells with values, so I can see some blank lines on my range, CopyToRange simply doesn't work and it is counting all lines, not just the visible ones. I am using Office 2010. Could you please help?

sub AutofilterToCopy

Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet
Dim rngCol As Long
Dim rngCount As Integer

    Set wsSheet1 = Sheet1
    Set wsSheet2 = Sheet2


wsSheet1.Select

[COLOR=#008000]'This Autofilter don't work[/COLOR]
    wsSheet1.Range("Q10:Q45").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=( _
        wsSheet2.Range("R10")), Unique:=True

 [COLOR=#008000]'This CountIF don't work[/COLOR]
[indent]rngCol = wsSheet1.Range("Q" & MaxRows).End(xlUp).Row[/indent]
[indent]rngCount = WorksheetFunction.CountIf(Range("Q10:Q" & rngCol), "<>""")[/indent]


If wsLine.FilterMode = True Then
    wsLine.ShowAllData
End If

 

[JLGWhiz]

See if this modified version will work.

Sub AutofilterToCopy()
Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet
Dim rngCol As Long
Dim rngCount As Integer
    Set wsSheet1 = Sheets("Sheet1")
    Set wsSheet2 = Sheets("Sheet2")
wsSheet1.Select
'This Autofilter don't work
    wsSheet1.Range("Q10:Q45").AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=wsSheet2.Range("R10"), Unique:=True
 'This CountIF don't work
rngCol = wsSheet1.Range("Q" & Rows.Count).End(xlUp).Row
rngCount = Application.CountIf(wsSheet1.Range("Q10:Q" & rngCol), "<>""")
If wsLine.FilterMode = True Then
    wsLine.ShowAllData
End If

 

[ElianaMG]

Hi JLGWhiz, thanks for the responde, but this part below is not counting every row in the range not only the visible ones, any idea?

See if this modified version will work.

rngCol = wsSheet1.Range("Q" & Rows.Count).End(xlUp).Row
rngCount = Application.CountIf(wsSheet1.Range("Q10:Q" & rngCol), "<>""")

 

[JLGWhiz]

Hi JLGWhiz, thanks for the responde, but this part below is not counting every row in the range not only the visible ones, any idea?

Too many quote marks. Try this:

rngCount = Application.CountIf(wsSheet1.Range("Q10:Q" & rngCol), "<>")