0

I'm using a for loop in VBA to get values from an excel sheet and then performing a certain action. The sheet has about 200 rows filled with a function that is pulling information from a Pivot table on another sheet. At the moment, only the first 39 rows are actually filled with values. The remaining 161 rows just have the functions in there. When I try to find count the number of rows for my For loop, it still counts the rows which are empty except for the function. I want to ignore these.

So for example, if B3, B4,...B39 equal "A", "B', ..."AM", and anything past B39 is

=IFERROR(IF(OR(DesignStepPivots!A45="",DesignStepPivots!A45="Grand Total"),"",DesignStepPivots!A45),"")

that these function values are not included in the count.

Here is the code i'm using:

rowcount = Sheets("TestData").Range("B" & Rows.Count).End(xlUp).Row
For i = 3 To rowcount
    'Do some stuff
Next i

What I would like to see is that the value for rowcount to be equal to 39 instead of 200.

PeterT
  • 8,232
  • 1
  • 17
  • 38
Robert
  • 183
  • 2
  • 13
  • 1
    `rowcount = Sheets("TestData").Range("B:B").Find(What:="*", After:=Sheets("TestData").Range("B1"),LookIn:=xlvalues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row` – Scott Craner May 21 '19 at 18:12
  • @ScottCraner, thank you. Ignore my other comment, i didn't refreshed the page before responding and i missed your answer. – Robert May 21 '19 at 18:25

0 Answers0