Make VBA Faster

Hey all together

Can someone help me making my VBA Code / Makro faster? Or isnt it possible at all?

It needs to work over more then 900'000 Cells. I'll just Post the Code, so someone who has time can take a look at it and maybe tell me how to improve it?

Also a problem i have is that i think it isnt finishing ever. It feels like its not stoping the loop at all. Then when i stop the macro manualy, it has finished the hole macro as expected (after about half an hour)

Thanks in advance :D

Please Login or Register  to view this content.

Thank you for the fast response and for the tip.
I'm pretty a noob in VBA, as seen in the code above :P when i'm replacing the .select stuff, what do i need to use instead? it seemed to me as an easy code for what i wanted to do..

I'm exporting an excel file from another Program called IS-E, and to evaluate this sheet, i made an huge formula before.

Now i'm copying the old format (Because the new release of the program is exporting the excel file in another way), putting the exported data in the new Sheet, here called "Auswertung", putting the formula ind W3:S92000, and then i need to delete al "empty-appearing" cells, because they arent really empty at all. and when they arent empty, i'm getting an #Value error. So there i think is it where it stays all the time. is this enough to give you a picture of what i am trying to reach?

Something like:

Please Login or Register  to view this content.

Thank you very much. That really was an improvement as far as i can say

Now a question. Is it possible to tell the macro it should look in the Sheet that it is importing of the other file, how many Cells are written, and than only apply the format pasting and the value pasting and, as the most important and time eating thing, deleting "empty-appearing" cells to that amount / range of cells? so not always all 92'000 if there are only, per example, 30'000 written in the Sheet that i want to evaluate? is that somehow possible?

Please Login or Register  to view this content.

?

Exactly what i wanted thank you so much, only tested it with a part of the code, but will insert it into the whole thing later

You may also find using something like:

Please Login or Register  to view this content.

to be quicker than:

Please Login or Register  to view this content.

You can do a similar thing with formats but it depends how much formatting there is as I think you need to change each property (color, border, border thickness, font, font size, font color etc) separately.

Yes :D it works how i wanted it to do, thanks guys.

Now i have one little last question: I want to import a bunch of .xlsx files into one file with different sheets. now i've already put a little code together, but i'm a little bit stuck with it. there are 4 excel files in the specific folder with different names. Is it possible to somehow do what i'm trying to do?

Please Login or Register  to view this content.

You probably want something along the lines of:

Please Login or Register  to view this content.

yes exactly

thank you so much for your help