Macro to select filtered data is inconsistent..

[Dahlia]

Dear Excel Experts,

I've been trying to find solution for this for almost a week but to no avail. Just really don't understand which part I've done wrong.

My code below should do copy to filtered data and paste them below the row (skip one blank row to differ) of the original data. The original data is in csv format which located in separate workbook. It has a checking command line to ensure that the filtered data is not blank, else skip and run next sub macro. This is needed to run through thousands of original data in separate workbooks. The weird thing that happens is that some files, the macro detects blank correctly, some won't and will prompt the error and stops the macro. I tried to change between count and countA for detecting the filtered data rows but if i do that the result will also be different, as if some of it is detected as text so, it will count in CountA but some will only be counted if using Count.. I have tried different version of Excel, ranging from 2010 up to 2016 but no difference.. I have tried many different macro commands to count only visible data rows but still don't give any difference.

This has been so much pain for me as I need to get accurate data to calculate SLA performance of each staff in attending tickets. Please, please, please help me..

For Each Sheet In ActiveWorkbook.Sheets
'Call tukardate
Columns("D:E").NumberFormat = "@"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    Range("A1").Select
    'LRow = Range("A" & Rows.Count).End(xlUp).Row
    LRow = Range("A1", Range("A1").End(xlDown)).Rows.Count
    'MsgBox LRow
    Set r = ActiveSheet.Range("$A$1:$F$" & LRow)
    r.AutoFilter Field:=5, Criteria1:=Array("PREPAID_SUPPORT", "28882"), Operator:=xlFilterValues
    
    'r.AutoFilter Field:=4, Criteria1:=Array( _
        "V03658X", "V01553X", "V03477X", "V03751X", "V03752X", "V04046X", "V04047X"), Operator:=xlFilterValues
    
    
    'LR = Range("A" & Rows.Count).End(.xlUp).Row
    'Range("A2:P" & LR).SpecialCells(xlCellTypeVisible).Select

    'jcell = WorksheetFunction.CountA(r.Cells.SpecialCells(xlCellTypeVisible))
    jcell = WorksheetFunction.CountA(ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible))
    'MsgBox jcell
    'wcell = WorksheetFunction.Count(r.Cells.SpecialCells(xlCellTypeVisible))
    wcell = WorksheetFunction.Count(ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible))
    'MsgBox wcell
    'MsgBox jcell
    If jcell = 0 Or wcell = 0 Then
    'If wcell = 0 Then
    If ThisWorkbook.Worksheets("Macros").Range("M1").Value = "" Then ThisWorkbook.Worksheets("Macros").Range("M1") = "1st Filter To Re-export:"
    MyRow = ThisWorkbook.Worksheets("Macros").Range("M" & Rows.Count).End(xlUp).Row
    'MsgBox MyRow
    ThisWorkbook.Worksheets("Macros").Range("M" & (MyRow + 1)).Value = FileName
    MyRow = ThisWorkbook.Worksheets("Macros").Range("M" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Worksheets("Macros").Range("$M$1:$M$" & MyRow).RemoveDuplicates Columns:=1, Header:=xlYes
'End If
    GoTo OtherFilter
    
    End If
    Range("A2:F" & LRow).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Rows((LRow + 2) & ":" & (LRow + 2)).Select
    ActiveSheet.Paste
    
    Selection.AutoFilter

GoTo OtherFilter

OtherFilter:
    'Range("D1").Select
    'Selection.AutoFilter
    
    Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    Range("A1").Select
    'LRow = Range("A" & Rows.Count).End(xlUp).Row
    LRow = Range("A1", Range("A1").End(xlDown)).Rows.Count
    'LRow = LRow - 2
    'MsgBox LRow
    Set r = ActiveSheet.Range("$A$1:$F$" & LRow)
    r.AutoFilter Field:=4, Criteria1:=Array( _
        "V03658X", "V01553X", "V03477X", "V03751X", "V03752X", "V04046X", "V04047X"), Operator:=xlFilterValues
    'r.AutoFilter Field:=5, Criteria1:=Array("PREPAID_SUPPORT", "28882"), Operator:=xlFilterValues
    
    'r.AutoFilter Field:=4, Criteria1:="V04047X", Operator:=xlFilterValues
    'kcell = WorksheetFunction.CountA(r.Cells.SpecialCells(xlCellTypeVisible))
    kcell = WorksheetFunction.CountA(ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible))
    'MsgBox kcell
    'vcell = WorksheetFunction.Count(r.Cells.SpecialCells(xlCellTypeVisible))
    wcell = WorksheetFunction.Count(ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible))

    'MsgBox vcell
    'MsgBox kcell
    If kcell = 0 Or vcell = 0 Then
    'If vcell = 0 Then
    If ThisWorkbook.Worksheets("Macros").Range("N1").Value = "" Then ThisWorkbook.Worksheets("Macros").Range("N1") = "2nd Filter to Re-export:"
    MyRow1 = ThisWorkbook.Worksheets("Macros").Range("N" & Rows.Count).End(xlUp).Row
    'MsgBox MyRow1
    ThisWorkbook.Worksheets("Macros").Range("N" & (MyRow1 + 1)).Value = FileName
    MyRow1 = ThisWorkbook.Worksheets("Macros").Range("N" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Worksheets("Macros").Range("$N$1:$N$" & MyRow1).RemoveDuplicates Columns:=1, Header:=xlYes
    GoTo OtherWsheet
    End If
    
    'Range("A" & LRow + 1).Select
    

    'cnt = Range("A" & (LRow + 2)).CurrentRegion.Rows.Count
    'MsgBox cnt
    'MsgBox LRow
    'nxtr = LRow + 1
    'MsgBox nxtr
    'LRow = LRow - 2
    Range("A2:F" & LRow).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    'cnt = LRow + 3
    LRow = Range("A" & Rows.Count).End(xlUp).Row
    'MsgBox cnt
    Rows(LRow + 1 & ":" & LRow + 1).Select
    'Rows(nxtr & ":" & nxtr).Select
    ActiveSheet.Paste
GoTo OtherWsheet

OtherWsheet:
'MsgBox LRow
   Range("A1").Select
   Selection.AutoFilter
   LRow = Range("A1", Range("A1").End(xlDown)).Rows.Count
   'MsgBox LRow
  'LRow = Range("A" & Rows.Count).End(xlUp).Row
  'If Range("A" & LRow).Value = "" Then Rows("2:" & LRow).Delete Else Rows("2:" & (LRow + 1)).Delete
  Rows("2:" & LRow + 1).Delete
   ' Range("A2").EntireRow.Delete
    'Rows("2:2").Select
   ' Range(Selection, Selection.End(xlDown)).Delete
   ' Rows("2:2").Delete
   LRow = Range("A1", Range("A1").End(xlDown)).Rows.Count
    ActiveSheet.Range("$A$1:$F$" & LRow).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6), Header:=xlYes
    LRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1").Select

[Richard Buttrey]

Almost impossible to comment without seeing the workbook. Please upload this and the csv file on which it is dependent.
Many of us would prefer to go back to basics since often we can find more efficient ways of achieving your goal.
So in a separate note clearly explain what you start with, what you want to do and what the results should be. Be sure to manually add the results and layout you expect to see on a separate worksheet. Once we can see the goal and what you start with we'll be able to comment further.

[Dahlia]

Hello Richard Buttrey,

Firstly, I really thank you for your swift response. After your reply, I suddenly had an idea to overcome this problem. I tested it and it worked just as I really need it. What I did was create another submacro which actually puts a formula to define "YES" if meet criterias either in col D or E. Then I filter data based on "YES". After that, followed by all my other formulas. So, only when I do this that I can have an accurate number of filtered data rows that match my criterias for each file and I don't encounter error anymore..

*phew* what a relief! After almost a week of sleepless nights.... only after I posted my problem here that I finally got the correct solution!.

Thanks to you and all who may have spent sometime to read my problem and trying to create for solution.

With this, I consider this issue is closed and solved.

Thank you for your time.

[jaslake]

Hi Dahlia

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.