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

 

[venkat1926]

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

The sample data is like this

Sheet1

*	A	B	C
hdng1	hdng2	hdng3
a	data	data
s	data	data
d	data	data
f	data	data
d	data	data
s	data	data
a	data	data
d	data	data
j	data	data

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

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Now manually filter for column1 criteria being “h”
Then you will get
Only heading row

Now run this macro

Sub test()
Dim rdata As Range, j As Long
Set rdata = Range("a1").CurrentRegion
j = rdata.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).Row
MsgBox j
End Sub

Msgbox will tell ypu last cell row is 1

you can remove filter and again auto filter for column one criteria being "a"
and then run the above macro. you willget j as 8 that is the last row containing "a" in column one

note the last row of the whole data is 10 and lst row filtered data is 8

So if you add code

If j>1 then
Copy filtered data
End if

Does the above give some idea how to modify your macro ????????
if j=1 there is only hdng row and so do not do anything

 

[Gard5096]

Hi Venkat, I appeciate your response and explanation.

I understand your strategt and code, but it doesn't seem to work the way I need it to.

The code below is an example of what it looks like now. It is still copying over the entire dataset when the filter results in zero rows. When I debug and hover over the "j" in the code, it shows a value of 13573 when I place it before the filter, and the same when I place it after the filter. Do you know why this is happening? I would think if I placed it after the filter, that it would equal 1.

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

With Worksheets("REPORT")

    Set rngData = Range("A1").CurrentRegion
    j = rngData.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).Row
    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 j > 1 Then
    .Range(.Cells(2, CLIENT), .Cells(LR, CLIENT)).Copy _
            Sheets("DailyReport").Range("A3")

Else:

      Goto SkipCopy
End If

End With

 

[Gard5096]

It seems as though the "j" only counts the rows after the file has been saved.... this means that it will not count the rows after the filter unless it is saved first. Is there a workaround to this?


Perhaps there is a way I can incorporate the CellTypeVisible into this variable below?
LR = .Cells(.Rows.Count, CLIENT).End(xlUp).Row

 

[Firefly2012]

If you want to determine if there are any results returned from a filter use:

If Activesheet.Autofilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then 
  'there are filtered rows, copy them somewhere:
  Activesheet.Autofilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Copy
  'to somewhere...  
Else
  'no filter results returned
End If

 

[venkat1926]

in the absence of your data I am not able to check your macro
the point I am getting at you is if you do autofilter Iwas using J only to show that special cells will give you filtered data. I never meant that you should use only my macro for your solution you use the idea.

I hope you know how to write the code for autofiltering

rdata.specialcells(xlcelltypevisible)

wil give you filtered dat

rdata.specialcells(xlcelltypevisible),.copy worksheets("sheet2").range("a2")

will copy filtered data to sheet 2.. try this as exercisse


if J>1 tlhen the above code waill copy if there is data after filtering

send your data and then annexs your macro I can check. if You got the solution following firefly then no more action is rquired.

 

[Gard5096]

How do I send the data? I don't see anywhere to attach.


Here is the entire code:
If you put a stop at the red text, and then check the value of "j", you will see that the it is still counting all rows even when all are hidden.

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
    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)
    i2 = Application.WorksheetFunction.Match("INVESTOR_TYPE", Range("1:1"), 0)
    
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
'Client ID
If j > 1 Then
[COLOR=#ff0000]    .Range(.Cells(2, CLIENT), .Cells(LR, CLIENT)).Copy _
            Sheets("Re-Employment > 30").Range("A3")
[/COLOR]Else:
    GoTo Spot
End If

'Loan Number
    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
    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
    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
    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")
End If

    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
End Sub

I have a test workbook created I can send over once I figure out how.

Thank you for your continuous attention.

 

[Gard5096]

Another note-

With the code:

rdata.specialcells(xlcelltypevisible),.copy worksheets("sheet2").range("a2")

you are copying the entire sheet. I have assigned each column a variable and only need to copy certain columns over after the filter.

 

[venkat1926]

I have a test workbook created I can send over once I figure out how.

select small portion of the data and paste it in the reply message area. Of course there isa an addein There is an add in in this newsgroup for posting the data. see
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970
this is useful addin and it would be useful to you to downlaod this addin and learn how to use it.

you are copying the entire sheet. I have assigned each column a variable and only need to copy certain columns over after the filter.

unsophisticatged non macarao way is after copying all the columns in the filtered data in another sheet and DELETING THE UNEANTED DATA
OR
warite a macro for deletingunwanted coluns in the second sheet.

when it is possible for you to send the data I shall check your latest mjacro

 

[venkat1926]

I have a test workbook created I can send over once I figure out how.

select small portion of the data and paste it in the reply message area. Of course there isa an addein There is an add in in this newsgroup for posting the data. see
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970
this is useful addin and it would be useful to you to downlaod this addin and learn how to use it.

you are copying the entire sheet. I have assigned each column a variable and only need to copy certain columns over after the filter.

unsophisticatged non macarao way is after copying all the columns in the filtered data in another sheet and DELETING THE unwanted columns
warite a macro for deletingunwanted coluns in the second sheet.

when it is possible for you to send the data I shall check your latest mjacro

see my sgnature area also