CODEWITHSUNDEEP
vbaexcel
YHTAN
YHTAN

Reputation: 686

VBA filter result row count wrong

Language: Excel VBA

Scenario: I have a source range (rngDTRef_AllRecord) that i need to insert the data into the destination range (rngRTDC_AllDetail)

for each of the row(rngCurrRow) in the source range (rngDTRef_AllRecord), it will filter the destination range (rngRTDC_AllDetail)

if the filter yield result, it will add some data to the result row (Note: each of the result is unique)

else it will add a new row to the destination range (rngRTDC_AllDetail)

below is the code:

    For Each rngCurrRow In rngDTRef_AllRecord.Rows
        intRTDC_RowBegin = 7
        intRTDC_ColIdxTotal = 20
        intRTDC_RowLast = fntGetNewLastRow 'this is some function get last row of rngRTDC_AllDetail due to might add in new row
        Set rngRTDC_AllDetail = shtRTDC.Range(shtRTDC.Cells(intRTDC_RowBegin, 1), shtRTDC.Cells(intRTDC_RowLast, intRTDC_ColIdxTotal))

        rngRTDC_AllDetail.AutoFilter
        rngRTDC_AllDetail.AutoFilter Field:=intRTDC_ColIdxAcc, Criteria1:=rngCurrRow.Cells(1, intDTSource_ColIdxAccCode), Operator:=xlAnd
        rngRTDC_AllDetail.AutoFilter Field:=intRTDC_ColIdxText, Criteria1:=rngCurrRow.Cells(1, strCurrAccCodeText), Operator:=xlAnd

        Dim rngResult As Range
        Set rngResult = rngRTDC_AllDetail.rows.SpecialCells(xlCellTypeVisible)'rngRTDC_AllDetail.SpecialCells(xlCellTypeVisible) also not work

        'after filter, it will be only 1 result or none
        If (rngResult.Rows.Count > 0) Then 
            'if the filter have result, do something here.
        else
            'add new row
        End If
    Next

My problem is after the filter, from the excelworksheet, i can see that have only 1 record, but rngResult.Rows.Count = 2 'for the first filter record (that have 1 row only) in rngRTDC_AllDetail, i suspect due to it include the header, but i am not sure what wrong.

rngResult.Rows.Count = 1 'for the rest of the filter record that have 1 row

even worse is when there is no record after the filter, rngResult.Rows.Count = 1

Any advice will be appreciate. TQ.

Upvotes: 2

Views: 1333

Answers (1)

YHTAN
YHTAN

Reputation: 686

Ok. After spent some time on it, I found out the solution already. Below is some note for who facing similar problem.

Objective: To insert "value" to columnC, when columnA = "a" AND columnB ="b" AND the row is between 1 to 10 only 

    A               B               C
1   columnA     |   columnB     |   ColumnC
2   a           |   b           |   value
3   a           |   x           |   
4   x           |   x           |
5   x           |   x           |
6   c           |   b           |
7   a           |   b           |   value
8   x           |   x           |
9   x           |   x           |
10  a           |   b           |   value
11  a           |   b           |   
12  a           |   b           |   
...

'insert value at columnC
ActiveSheet.Range("A1:B10").AutoFilter Field:=1, Criteria1:="a", Operator:=xlAnd
ActiveSheet.Range("A1:B10").AutoFilter Field:=2, Criteria1:="b", Operator:=xlAnd

Dim rng As Range
For Each rng In ActiveSheet.AutoFilter.Range.Range("A1:B10").SpecialCells(xlCellTypeVisible).Rows
    If (rng.Row <> 1) Then 'no the header
        ActiveSheet.Cells(rng.Row, "c") = "value" 'set value at C2,C7,C10
    End If
Next rng

'count the total row visible 
Dim rngA As Range
Set rngA = ActiveSheet.AutoFilter.Range.Range("A1:B10")
Debug.Print rngA.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1   'result 3
'Reference:http://www.contextures.com/xlautofilter03.html

Note1**: "ActiveSheet.AutoFilter.Range" will always include the header and all below row as visible row.

Note2**: "ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows" will offset the range 1 row below only, not suitable if you need to set the value at the result row.

Upvotes: 1

Related Questions