0

I have a little problem with vba. I want to use Rows.Count but it will not count right.

In my sheet "thisyear" I want to filter some project numbers. When I filter I will get a list that has a different lenght, sometimes there are 5 rows in one project number and sometimes 10. Also sometimes it´s empty. That´s why I select all rows from 2 to 100, cut off all empty rows and than want to use the Rows.Count function. But it will not count in the right way. In project-number1 are 3 rows, but the msgBox give me the number 99 and I don´t know why.

Sheets("thisyear").Range("$A$1:$J$735").AutoFilter Field:=4, Criteria1:= _
    "project-number1"
Rows("2:100").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
MsgBox Selection.Rows.Count

I hope someone can help me!

The complete code:

Sheets("August").Range("$A$1:$J$735").AutoFilter Field:=4, Criteria1:= _
    "project-number1"
Rows("2:3000").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
If Selection.Rows.Count >= "30" Then
Sheets("Daten-August").Select
LastCellColRef = 1
Set LastCell = Sheets("Daten-August").Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)
Range(LastCell.Address).Select
ActiveCell.FormulaR1C1 = "_"
Else
Selection.Copy
Sheets("Daten-August").Select
LastCellColRef = 1
Set LastCell = Sheets("Daten-August").Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)
Range(LastCell.Address).Select
Sheets("Daten-August").Paste
Set LastCell = Sheets("Daten-August").Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)
Range(LastCell.Address).Select
ActiveCell.FormulaR1C1 = "_"
End If
Keffler
  • 1
  • 1
  • `xlCellTypeConstants` will return all cells that have a constant value (no formula) also the ones that are hidden by the filter. – Pᴇʜ Sep 29 '20 at 08:21
  • thats a nice thing to know, thanks. but when I use Rows("2:1000").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select MsgBox xlCellTypeConstants it gives me back 2, allways – Keffler Sep 29 '20 at 08:31
  • If you `Rows("2:100").Select` Excel does not know in which sheet to select these rows. You always need to specify the sheet `Sheets("thisyear").Rows("2:100").Select` Also avoid using `Select`: You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Sep 29 '20 at 08:35
  • I used: Sheets("thisyear").Range("$A$1:$J$735").AutoFilter Field:=4, Criteria1:= _ "KEV" Rows("2:100").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select MsgBox Selection.Rows.Count / MsgBox xlCellTypeConstants ... Rows gave me back "99" and xlCell "2", but it should be "0", because it´s empty – Keffler Sep 29 '20 at 08:42
  • 1
    Does [THIS](https://stackoverflow.com/questions/17285897/row-count-on-the-filtered-data) help you? – Siddharth Rout Sep 29 '20 at 08:55
  • Siddharth Rout, yes that helps, thanks. But would be easier is there a funktion I can use that just count the rows I selected. Like `Selection.Rows.Count` but different. I don´t want that the code will be to long,I have to do the same thing 50 times or so for all projects. Sorry I´m a beginner in VBA. – Keffler Sep 29 '20 at 09:03
  • You can create a common `Function CountRows(rng as Range) As Long` and place it in module. This way you can call it by `MsgBox CountRows(filteredrange)`. Also like @Pᴇʜ mentioned, avoid the use of `Select`. See the link he gave which also stresses about avoiding the use of `Selection` – Siddharth Rout Sep 29 '20 at 09:07

0 Answers0