If AutoFilter Hides All, .Rows.Count Still Counts All

[Gard5096]

If AutoFilter Hides All, .Count Finds Row 8000 Rather than Row 1

Hello,

I have a macro that performs an autofilter on a table, and then finds the last row using .Count in order to copy the column to another sheet. This has been working fine, but then I came across a problem. If the filter ends up hiding all of the rows, the .rows.count method ends up finding the last row in the worksheet, rather than finding row 1 which is the only row visible.

Does anyone know why this is happening, and how I can fix the problem?


Below is an example of what my code looks like. When the AutoFilter hides all rows except the title bar, it should not copy a range... instead it copies all 8000 files that were visible before the autofilter. After testing, LR is coming up as the last row in the worksheet prior to the autofilter.

Dim rngData As Range, LR As Long, CLIENT as Long

With Worksheets("REPORT")

    Set rngData = Range("A1").CurrentRegion
    CLIENT = Application.WorksheetFunction.Match("Client_ID", Range("1:1"), 0)
    rngData.AutoFilter Field:=Active, Criteria1:="A"
    LR = .Cells(.Rows.Count, CLIENT).End(xlUp).Row


If LR > 1 Then
    .Range(.Cells(2, CLIENT), .Cells(LR, CLIENT)).Copy _
            Sheets("DailyReport").Range("A3")
End If

End With

 

[Gard5096]

Ok I have pasted some of the data below. Please make sure you format it as a table so it will have the same rules that I am dealing with. The tab that it pastes to is named "Re-Employment > 30". The code from my previous post is what I am using. Thank you again.

[TABLE="width: 1264"]
<TBODY>[TR]
[TD]REPORTDATE</SPAN>[/TD]
[TD]LOAN_NUMBER</SPAN>[/TD]
[TD]PARTITIONCD</SPAN>[/TD]
[TD]LOSS_MIT_STATUS_CODE</SPAN>[/TD]
[TD]DW_ASSIGNEDRM_EMP_MGR</SPAN>[/TD]
[TD]DW_ASSIGNEDRM_EMP_NAME</SPAN>[/TD]
[TD]WORKBASKET</SPAN>[/TD]
[TD]LOSS_MIT_SET_UP_DATE</SPAN>[/TD]
[TD]979</SPAN>[/TD]
[TD]INVESTOR_TYPE</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]1281263</SPAN>[/TD]
[TD="align: right"]854[/TD]
[TD]A[/TD]
[TD]SUP JOHN[/TD]
[TD]Tommy[/TD]
[TD]MonitorPaymentsWB[/TD]
[TD="align: right"]1/28/2013[/TD]
[TD="align: right"]3/12/2013[/TD]
[TD]FNMA[/TD]
[/TR]
[TR]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]1018365</SPAN>[/TD]
[TD="align: right"]771[/TD]
[TD]A[/TD]
[TD]SUP STEVE[/TD]
[TD]Ron[/TD]
[TD]MonitorPaymentsWB[/TD]
[TD="align: right"]1/29/2013[/TD]
[TD="align: right"]3/13/2013[/TD]
[TD]ASSET[/TD]
[/TR]
[TR]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]101854</SPAN>[/TD]
[TD="align: right"]854[/TD]
[TD]A[/TD]
[TD]SUP KAREN[/TD]
[TD]Elise[/TD]
[TD]MonitorPaymentsWB[/TD]
[TD="align: right"]1/30/2013[/TD]
[TD="align: right"]3/14/2013[/TD]
[TD]ASSET[/TD]
[/TR]
[TR]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]1214030</SPAN>[/TD]
[TD="align: right"]854[/TD]
[TD]A[/TD]
[TD]SUP KAREN[/TD]
[TD]Craig[/TD]
[TD]MonitorPaymentsWB[/TD]
[TD="align: right"]1/31/2013[/TD]
[TD][/TD]
[TD]QVA[/TD]
[/TR]
[TR]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]129557</SPAN>[/TD]
[TD="align: right"]771[/TD]
[TD]A[/TD]
[TD]SUP KAREN[/TD]
[TD]Craig[/TD]
[TD]MonitorPaymentsWB[/TD]
[TD="align: right"]2/1/2013[/TD]
[TD][/TD]
[TD]FNMA[/TD]
[/TR]
[TR]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]139154</SPAN>[/TD]
[TD="align: right"]854[/TD]
[TD]A[/TD]
[TD]SUP KAREN[/TD]
[TD]Elise[/TD]
[TD]MonitorPaymentsWB[/TD]
[TD="align: right"]2/2/2013[/TD]
[TD][/TD]
[TD]QVA[/TD]
[/TR]
[TR]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]1023533</SPAN>[/TD]
[TD="align: right"]854[/TD]
[TD]A[/TD]
[TD]SUP KAREN[/TD]
[TD]Craig[/TD]
[TD]MonitorPaymentsWB[/TD]
[TD="align: right"]2/3/2013[/TD]
[TD="align: right"]3/18/2013[/TD]
[TD]FHLMC[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[venkat1926]

your data is like this ( I used the addin available in this group)

Report

*	A	B	C	D	E	F	G	H	I	J
REPORTDATE	LOAN_NUMBER	PARTITIONCD	LOSS_MIT_STATUS_CODE	DW_ASSIGNEDRM_EMP_MGR	DW_ASSIGNEDRM_EMP_NAME	WORKBASKET	LOSS_MIT_SET_UP_DATE	INVESTOR_TYPE
A	SUP JOHN	Tommy	MonitorPaymentsWB	FNMA
A	SUP STEVE	Ron	MonitorPaymentsWB	ASSET
A	SUP KAREN	Elise	MonitorPaymentsWB	ASSET
A	SUP KAREN	Craig	MonitorPaymentsWB	*	QVA
A	SUP KAREN	Craig	MonitorPaymentsWB	*	FNMA
A	SUP KAREN	Elise	MonitorPaymentsWB	*	QVA

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]979[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]1281263[/TD]
[TD="align: right"]854[/TD]

[TD="align: right"]1/28/2013[/TD]
[TD="align: right"]3/12/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]1018365[/TD]
[TD="align: right"]771[/TD]

[TD="align: right"]1/29/2013[/TD]
[TD="align: right"]3/13/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]101854[/TD]
[TD="align: right"]854[/TD]

[TD="align: right"]1/30/2013[/TD]
[TD="align: right"]3/14/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]1214030[/TD]
[TD="align: right"]854[/TD]

[TD="align: right"]1/31/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]129557[/TD]
[TD="align: right"]771[/TD]

[TD="align: right"]2/1/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]3/20/2013[/TD]
[TD="align: right"]139154[/TD]
[TD="align: right"]854[/TD]

[TD="align: right"]2/2/2013[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


I have SLIGHTLY modified the macro with some comments in between. when you write a macro introduce amny message boxes which can be removed after testing the macro

Sub Example2()


Dim i, i2, v, v2, v3, v4, v8, Active, CLIENT, LR As Long
Dim rngData As Range
Dim j As Long
With Worksheets("REPORT").Activate
'   you are activating sheet so "with" and "end with" can be removed. if you use width there should be dots in front in subsequent lines
   
    Set rngData = Range("A1").CurrentRegion
    v = Application.WorksheetFunction.Match("LOAN_NUMBER", Range("1:1"), 0)
    v2 = Application.WorksheetFunction.Match("DW_ASSIGNEDRM_EMP_MGR", Range("1:1"), 0)
    v3 = Application.WorksheetFunction.Match("DW_ASSIGNEDRM_EMP_NAME", Range("1:1"), 0)
    v4 = Application.WorksheetFunction.Match("WORKBASKET", Range("1:1"), 0)
    v8 = Application.WorksheetFunction.Match("LOSS_MIT_SET_UP_DATE", Range("1:1"), 0)
    Active = Application.WorksheetFunction.Match("LOSS_MIT_STATUS_CODE", Range("1:1"), 0)
    CLIENT = Application.WorksheetFunction.Match("PARTITIONCD", Range("1:1"), 0)
    i = Application.WorksheetFunction.Match(979, Range("1:1"), 0)
    MsgBox i
    '979 is number I removed quotes
    i2 = Application.WorksheetFunction.Match("INVESTOR_TYPE", Range("1:1"), 0)
    MsgBox i2
End With


'Filter
With Worksheets("REPORT")
    rngData.AutoFilter Field:=Active, Criteria1:="A"
    rngData.AutoFilter Field:=v4, Criteria1:="MonitorPaymentsWB"
    rngData.AutoFilter Field:=i2, Criteria1:=Array("FNMA", "FHLMC", "ASSET", "PRIVATE"), Operator:=xlFilterValues
    rngData.AutoFilter Field:=i, Criteria1:="<" & Date - 30
    LR = .Cells(.Rows.Count, CLIENT).End(xlUp).Row
    j = rngData.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).Row
MsgBox LR


'Client ID
'not necessary inview of LR
'If j > 1 Then
'    .Range(.Cells(2, CLIENT), .Cells(LR, CLIENT)).Copy _
'            Sheets("Re-Employment > 30").Range("A3")
'Else:
'    GoTo Spot
'End If


'Loan Number
'why repeat defn of LR
   ' LR = .Cells(.Rows.Count, v).End(xlUp).Row
If LR > 1 Then
    .Range(.Cells(2, v), .Cells(LR, v)).Copy _
            Sheets("Re-Employment > 30").Range("B3")
End If


'Supervisor
'why again LR
    'LR = .Cells(.Rows.Count, v2).End(xlUp).Row
If LR > 1 Then
    .Range(.Cells(2, v2), .Cells(LR, v2)).Copy _
            Sheets("Re-Employment > 30").Range("C3")
End If


'RM
'why again LR
    'LR = .Cells(.Rows.Count, v3).End(xlUp).Row
If LR > 1 Then
    .Range(.Cells(2, v3), .Cells(LR, v3)).Copy _
            Sheets("Re-Employment > 30").Range("D3")
End If


'Re-Employment Date
'why again LR
 '   LR = .Cells(.Rows.Count, i).End(xlUp).Row
If LR > 1 Then
    .Range(.Cells(2, i), .Cells(LR, i)).Copy _
            Sheets("Re-Employment > 30").Range("E3")
'wheat to do of LR is not greater than 1
Else
GoTo Spot


End If
'you used "with" for this sheet. once you use WITH function you need not actiave
'that is advantage of using with function
   ' Sheets("Re-Employment > 30").Select
End With


'Filldown with number of days since re-employment
With Worksheets("Re-Employment > 30")
    With .Range("F3", Range("E" & Rows.Count).End(xlUp).Offset(, 1))
        .FormulaR1C1 = "=Today()-RC[-1]"
        .Value = .Value
    End With
End With
 
 
Spot:
''there would be other tabs to do a similar filter and copy/paste from the Report tab
'should you not remove autofilter
Worksheets("report").Activate
ActiveSheet.AutoFilterMode = False


End Sub

the macro can be tweaked some more. But your logic is ok. In this sample data when you filter no data except heading row comes. I presume in your full data sone data will remain after filtering. You are welcome to ask any doubts.