VBA - Count filtered column lines and display the value in a cell

Asked

Viewed 298 times

0

Hello friends I am banging my head in a vba code, what I want and when I Filter a column for example "A1" it counts all visible cells, ignore hidden cells and stop counting when find the first empty cell and print the value counted in cell "B12", below the code it even works but I have to put a wider range than the column data, and it ends up counting the blank of the range along with the visible ones I want.

Sub CountVisRows()

Range("b12").Value = ActiveSheet.Cells(1, 1).Range("A14:A4000").Cells.SpecialCells(xlCellTypeVisible).Count

End Sub
vba excel-vba

2 answers

0

I would settle this simply like this:

Sub somarFiltradas()

  Dim cont As Integer
  Dim i As Integer

  cont = 0
  i = 3

  While Cells(i, 4).Value <> ""

      If Cells(i, 4).EntireRow.Hidden = False Then
    
          cont = cont + 1
        
      End If
    
      i = i + 1
    
  Wend

  Cells(1, 4).Value = cont

End Sub

  • Thank you for helping but it is returning me value 0, when I filter the column I would like it to count only the visible cells and ignore the hidden cells and show the value in a cell from the "A" column "14" on until I find the empty A column cell.

  • This is exactly what it does. You need to change the columns and rows for your spreadsheet. I just made a template. If the column to filter is "A" then change the code of While Cells(i, 4). Value for While Cells(i, 1). Value, because it is the first column. And you need to adjust the value of " i " to the first row where your data starts after column labels. For example, if the first record is on line 2, you change it to i = 2.

  • And for the total change Cells(1, 4). Value = cont to desired column and row. (in my example is row 1 and column 4, which is the same thing as D1.

0

Hello, in place of Range("A14:A4000") vc can save the table to a listObject and use Listcounm(column number).

It would be something like that:

Dim loTabela As ListObject
Set loTabela = Range("nomeTabela").listObject

'Usei o numero 1 para representar o A1
Range("b12").Value = loTabela.ListColunms(1).Range.SpecialCells(xlCellTypeVisible).Cells.Count

Browser other questions tagged vba excel-vba

You are not signed in. Login or sign up in order to post.