0

I have a VBA Macro that is working with a very large dataset. In my dataset I have ~44000 rows. I want to count this within the macro and have tried to use the top methods shown here.

I take variable sncountmax and make it equal to one of the methods in the link above.

sncountmax = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Even though the sheet contains 44000 rows sncountmax will remain set to whatever it was previously set to.

EDIT: I have since checked and no other macro functions are executing on this sheet. I tried to remove duplicates using the macro and this did not work, but removing the duplicates in Excel did.

Community
  • 1
  • 1
Lewis Heslop
  • 574
  • 2
  • 8
  • 25
  • Broken link, whatever its supposed to point to should probably be in the question. – Alex K. Mar 10 '16 at 12:43
  • Link fixed. Sorry for not including it here explicitly but the link points to the code that I have used here – Lewis Heslop Mar 10 '16 at 12:44
  • @LewisHeslop I cannot reproduce your problem with the limited information you have provided. So the problem likely lies in other lines of code that you do not share with us; your data; the defaults for the `.Find` method that you have not explicitly set, or possibly something in your environment. – Ron Rosenfeld Mar 10 '16 at 12:53
  • @LewisHeslop As an example, since you did not specify it, you may get a different return value depending on the saved value of the `Lookin` constant and the nature of your data. Also, the last row will be that on whatever sheet is active at the time you run this macro. – Ron Rosenfeld Mar 10 '16 at 13:01
  • The macro is acting on an excel workbook with multiple sheets, the macro grabs data from hundreds of xml files and orders them under headings, one of the headings is a serial number heading. Once all xml files have been grabbed I am trying to count the number of rows. – Lewis Heslop Mar 10 '16 at 13:07
  • @LewisHeslop If the comment above is directed to me, it does not address any of the issues I have raised. – Ron Rosenfeld Mar 10 '16 at 13:09
  • @RonRosenfeld It was more general. What information would you like me to provide? – Lewis Heslop Mar 10 '16 at 13:11
  • That should be clear from my comments. Also please read help for how to provide an example. – Ron Rosenfeld Mar 10 '16 at 13:29
  • 1
    Is the sheet you're trying to find the last row on the active sheet? Your code states 'Cells.Find(....)' - without telling it what sheet to work on it finds the last row on the currently active sheet. – Darren Bartrup-Cook Mar 10 '16 at 13:37
  • Once you have the code running - it will fail if there is no data on the sheet as it tries to find row 0. I find it easier to pass the whole thing out to a function. – Darren Bartrup-Cook Mar 10 '16 at 13:40

2 Answers2

2

Try changing to the following:

sncountmax = Cells(Rows.Count, "A").End(xlUp).Row

This will give the last row in Column A, so in your situation scountmax = 44000

TheGuyThatDoesn'tKnowMuch
  • 1,240
  • 1
  • 17
  • 28
  • 2
    They both work. I like the `"A"` method just because I don't always want to count the columns in a scenario where I need to count the rows in Column AQ ;) – TheGuyThatDoesn'tKnowMuch Mar 10 '16 at 12:58
  • 2
    The main reason of using `Cells` is that it calculates ~33% faster than `Range` if used with just numerical values. But using it with strings will make it slower than `Range`. While it won't matter in this case, but if you want to use the "A" Then you will be faster by using `sncountmax = Range("A" & Rows.Count).End(xlUp).Row` :P – Dirk Reichel Mar 10 '16 at 13:04
  • I am currently restarting my macro, if my problems persist, I will try this. I have a feeling this will be unrelated, because as I said in my edit, other lines of the macro were not being executed correctly. – Lewis Heslop Mar 10 '16 at 13:04
  • @DirkReichel if you have justification or tests that will prove information provided in your comments please provide it, otherwise it's just words – Vasily Mar 10 '16 at 13:31
  • What @Dirk Reichel is saying makes perfect sense, and I agree that searching by integers is generally faster than searching by characters. That being said, in this case it won't make a noticeable difference. But I do like the `sncountmax = Range("A" & Rows.Count).End(xlUp).Row` version he provided – TheGuyThatDoesn'tKnowMuch Mar 10 '16 at 14:25
0

i wouldn't advise using find. Its not reliable as for sometimes it can return nothing depending on Data, and it's slower.

If your sole purpose is counting the lines in the first column :

with thisworkbook.sheets("Sheet1")
    sncountmax = .Cells(.Rows.Count, 1).End(xlUp).Row
end with

Reference the sheets and workbooks like the above example.

You might want to use Option Explicit on the top of your module.

Also, i can only guess, but you should remove your Òn Error Resume Next. Errors are meaning your code is wrong somewhere.

Patrick Lepelletier
  • 1,596
  • 2
  • 17
  • 24