My macro runs endlessly & have to force excel to close to stop it

Hi all,

This is my first post in this forum & i am new to macro, therefore please excuse my lack of technical knowledge in this area.

I have a macro that i use to transpose contact information that is in vertical column to horizontal rows. Once the contacts are transposed to horizontal rows the macro then goes on to remove the empty rows that are between the between the contacts. However when i run the macro it runs endlessly instead of stopping when there is no more data, which in in this case is at row number 1884. Why will the macro not stop when there is no more data & how does the macro need to be changed to make it stop at the last contact?

Please Login or Register  to view this content.

You are falling in to infinite loop.

Do Until IsEmpty(ActiveCell)

Until the active cell becomes empty, the code will go on looping. It will only stop when you run out of memory or use brute force.
If you are new to programming, avoid while and do loops, instead use for loop which has a start and end points.

Thank you for your response.
Like i said i have only been at this for a day.
Are recommending that the following code be removed?

Please Login or Register  to view this content.

If so could you let me know what code should be inserted & where it should go in my macro?

No,

Do Until IsEmpty(ActiveCell)

saying that going on looping until the active cell which is where the cursor happens to be is empty. In other words, the code will keep looping until this condition is met. The reason for endless loop is you keep copying and active cell will never be empty. So, the code searching for empty cell and keep searching until it finds empty.
This code is from recoded macro. If you could attach a sample, I will give you different ways of looping.

Thanks again, trying hard to get my head around this.

Here is some sample contact information from the vertical column. Note that a contact information takes up four lines:
Gamini Abeysinghe Abr
Re/Max Diamond Realty Inc.
1570 Kipling Avenue
Toronto, Ontario M9R2Y1
Wasantha Abeysiri
Homelife Landmark Realty Inc.
7240 Woodbine Ave Unit 103
Markham, Ontario L3R1A4
Azhar Abrar
Kingsway Real Estate Brokerage
151 City Centre Drive #300
Mississauga, Ontario L5B1M7
Leonor Agosto Henry
Keller Williams Advantage Realty
1238 Queen St East Unit B
Toronto, Ontario M4L1C3

Then when i transpose it to one row of information for each contact, i want to look like this:
Gamini Abeysinghe Re/Max Diamond Realty Inc. 1570 Kipling Avenue Toronto, Ontario M9R2Y1
Wasantha Abeysiri Homelife Landmark Realty Inc. 7240 Woodbine Ave Unit 103 Markham, Ontario L3R1A4
Azhar Abrar Kingsway Real Estate Brokerage 151 City Centre Drive #300 Mississauga, Ontario L5B1M7
Leonor Agosto Henry Keller Williams Advantage Realty 1238 Queen St East Unit B Toronto, Ontario M4L1C3

And the macro needs to remove all the three rows that are empty, in between each contact.

Here is the code again:

Please Login or Register  to view this content.

This might work... Happy to explain if you cannot follow th elogic.

Please Login or Register  to view this content.

The main reason for infinite loop was of the active cell. If the curosor was in A1, you would not got the problem.

Please Login or Register  to view this content.

Thank you.
Could you explain?
I copied & pasted this code into the macro & tried it but nothing happened.
Just to clarify, my contact information is currently all in the B Column with four vertical cells containing the contact
infor for each contact. I want to transpose it to this contact information to so that it is in one horizontal row across
four columns (column C to F). When the data is transposed from the vertical column to a row there are three empty rows between
each contact, which i want the macro to remove.

Please Login or Register  to view this content.

Thank you,
Does any part of my current macro need to be removed & this inserted in its place?
or should i just copy & paste this code to the end of my current macro?

No, this is a complete new code. Just copy the code in to a module.
The code does not create or delete empty rows.

Or, you want like

Please Login or Register  to view this content.

Unbelievable, that fixed the problem!
I take my hat off to you sir.
Could you explain the logic to me?
I have been working on this for 12 hours straight & was at my wits end.
Do not know how to how grateful i am for your helP.
Thank you, so much!

Unbelievable, that fixed the problem!
I take my hat off to you sir.
Could you explain the logic to me?
I have been working on this for 12 hours straight & was at my wits end.
Do not know how to how grateful i am for your helP.
Thank you, so much!