VBA Code Efficiency

[AAP]

Hi ,

There are two differences :

1. The first statement treats the entire range of data as one entity ; thus even though you are referring to column A , if the last visible cell is Z1129 , this will return the value of 1129 for lastrow.

2. The second statement specifically looks at column A ; so if column A has data till row 10 , it will return the value of 10 for lastrow , even though some other column in the range may have data till row 1129.

Thus , the second statement should be used only if you are sure the column which is used does have data in it , and it has data where other columns may or may not have data.

At the same time , the first one is also not very reliable.

See this link for more information :

http://www.rondebruin.nl/win/s9/win005.htm

It all depends on how well you know your own worksheet ! If you are sure there will not be any problem using the first , use it ; the same goes for the second also.

Of course , as far as efficiency goes , I think both are equally efficient.

Narayan

Hi Narayan,

Great advice, Many thanks!

Amit

 

[AAP]

Hi Narayan,

I need a peice of adivce again. I tested this macro on 100,000 rows and works fine and it takes around 30 minutes to complete. Is there a way to tell user that code is running because when it runs excel get hang and user think that its not working and mistakenly close because of hanging. Someone suggest me to use [DOEVENTS]. Could you please shed some light on this suggestion. Many thanks.

 

[NARAYANK991]

Hi Amit ,

We can make use of the Excel Status Bar to display a message ; see if this is OK.

Narayan

 

[AAP]

Hi Amit ,

We can make use of the Excel Status Bar to display a message ; see if this is OK.

Narayan

Wow, this worked awesome. You are great! Narayan many many thanks