End(xlDwon).Row Not giving the correct value

**WarEagleWire** · 07-20-2012, 12:45 AM

I am writing a Macro to find the first blank row at the of a list. It worked great when I tested it on small file. Once I tried to implement it on a large file with 94890 rows, I am not getting the correct value. I have tried many ways of debugging to find out what the problem is but I cant seem to figure it out. I will copy my code and maybe someone can help with this issue. I keep getting the row value to be 29, it will not count any higher than that. In the code I commented out the screen updating and added a message box to allow myself to see the value. Sorry if the code is not embedded but I am new to this.

Please Login or Register  to view this content.

Thanks for the help

**MarvinP** · 07-20-2012, 12:50 AM

Hi WarEagleWire and welcome to the forum.

If Cell A30 is blank your code will stop on A29. Look to see if A30 is blank and that is the reason. Fill A30 in with some stuff and run the code again. What happens then?

**WarEagleWire** · 07-20-2012, 12:55 AM

Thanks for the fast reply and I'm happy to be here. I'm sure I will be on here a lot.

I double checked and it is not blank. The first set of code I made worked fine but only 5 cells were filled in. Then I tried on the large file and it kept giving me 29. I then created a third file that has 44 lines in it and it still gives me 29.

**MarvinP** · 07-20-2012, 01:19 AM

The code of

Please Login or Register  to view this content.

selects the Vin Sheet and sets NextRow to A1 and then press a ctrl+downarrow and one more cell down. I think you must be thinking that because Column B or C goes farther down it should move farther. If you stop on Cell A29 then A30 is blank. Do it by hand and see what happens.

What exactly comes up 29? Is the variable NextRow coming up 29? Are you looking at column A for the blank cell?

**WarEagleWire** · 07-20-2012, 01:44 AM

When I do ctrl+downarrow, I end up on row 43. I am trying to find the next cell in column A. I am not worried about B and C until I find the last row in A. I was wondering if anyone knew if this was some type of bug that excel has. The variable NextRow comes up as 30 when + 1 is added to the end and as 29 if I remove the + 1. The orginal sheet I was trying to work with has 94890 rows. I tried setting the cell to A94880 and when i did that, the value was coming up as 1048576. Bascially the purpose of this file is for vlookup. However, if the vlookup gives an error then I want to call this sub and add values to the end of the list. I have tried multiple files with this and the only ones that have worked have been files that have less than 30 rows in them. I will try setting the file to 28 rows and see if it works. I appreciate your help, I am just as stumped as you which is why i thought i would reach out for some help. Hopefully if I keep getting questions like yours, I will eventually find my mistake.

**MarvinP** · 07-20-2012, 01:56 AM

Check which workbook and worksheet you are working on. Set a breakpoint and use the immediate window to check with every line.

**protonLeah** · 07-20-2012, 02:03 AM

Experiment:
Change:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

What happens?

**WarEagleWire** · 07-20-2012, 02:16 AM

I think I figured out the problem. It is looking at a sheet in the workbook that the code is written in, not the sheet that I am calling. With the code

Please Login or Register  to view this content.

it is using my workbook with code. How can I change that code to make it be the workbook that I just opend in the previous line?

**WarEagleWire** · 07-20-2012, 02:21 AM

Thanks to everyone for the ideas and help. I knew that it was something stupid I was doing. I changed ThisWorkbook to wb and it work great.

**Cutter** · 07-20-2012, 11:35 AM

@ WarEagleWire Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself.
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.