0

I am trying to get the filtered rows count in the excel sheet.But I am trying below mentioned error :

Unable to get the SpecialCells Property of the Range class.

strPath="C:\Users\PSin\Desk\CodeInven.xlsx"

Dim ObjectName


ObjectName=Trim(InputBox("Object Name:"))
Set objExcel= CreateObject("Excel.Application")
objExcel.Visible= True

objExcel.Workbooks.Open(strPath)

With objExcel.Activeworkbook.Sheets("All")

    .Range("A1").AutoFilter 19,"="&ObjectName

Rowz = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1 'Not able to figure out the correct way

msgbox Rows


End With
Praveenks
  • 1,436
  • 9
  • 40
  • 79

1 Answers1

0

If you are just looking for a row count, you can dispense with cutting down the examined range and just get a count of the visible rows.

Dim Rowz
With objExcel.ActiveWorkbook.Sheets("All")
    With .Cells(1,1).CurrentRegion
        .AutoFilter 19, "=" & ObjectName
        Rowz = .Cells.SpecialCells(12).Rows.Count - 1 '<~~ VBScript might not know that xlCellTypeVisible = 12
        MsgBox Rowz
    End With
End With

I've reduced the xlCellTypeVisible to its value and removed the column A restriction by adding a restriction to the Range.CurrentRegion property. (btw, your original code was MsgBox Rows, not MsgBox Rowz. Probably best to use Option Explicit to avoid typos like that).

  • Thanks for response but I am getting 0 in the msgbox but there are some filtered values in the excel. – Praveenks Dec 29 '15 at 12:27
  • Do you have any fully blank rows? Check the documentation of the [Range.CurrentRegion property](https://msdn.microsoft.com/en-us/library/office/ff196678.aspx) I provided so you understand it. I had to guess a bit as you provided no sample data. (also check edit above) –  Dec 29 '15 at 12:40