Infinite Loop Issue

Scripting Forums AppleScript | Mac OS X
#1

I am writing an AppleScript for Excel and I can not seem to get rid of this pesky loop no matter what I do.

tell application "Microsoft Excel"
	activate
	select range "A1"
	set FoundResult to false
	try
		set FoundCell to (find range "A:A" what "")
		set FoundResult to true
		delete entire row of FoundCell
	end try
	repeat while FoundResult is true
		set FoundResult to false
		try
			set FoundCell to (find range "A:A" what "")
			set FoundResult to true
			delete entire row of FoundCell
		end try
	end repeat
end tell

Any help will be appreciated greatly.

#2

So this part never errors:

       try
           set FoundCell to (find range "A:A" what "")
           set FoundResult to true
           delete entire row of FoundCell
       end try
#3

escuse me. It never errors before this statement:

set FoundResult to true
#4

I don’t get any errors it just runs forever and ever…:confused:

#5

Hello.

There is probably a better way to do this. However, the code below should work: I search for the first non-empty row, and if that row has an index of 1 then there was just empty rows below, and the pruning of empty rows in between the area is finished, so I then exit the repeat loop.

There is probably a better way, using areas or something, it may even be written a better technique in Excel Script Reference.

tell application "Microsoft Excel"
	activate
	select range "A1"
	set FoundResult to false
	
	set FoundCell to (find range "A:A" what "")
	set FoundResult to true
	delete range (entire row of FoundCell) shift shift up
	
	repeat while FoundResult is true
		set FoundResult to false
		try
			set FoundCell to (find range "A:A" what "")
			set first_Empty to first row index of FoundCell
			delete entire row of FoundCell
			set FoundProbe to (find range "A:A" what "*" after FoundCell)
			set first_full to first row index of FoundProbe
			if first_full < first_Empty then
				set FoundResult to false
			end if
		on error
			set FoundResult to false
		end try
	end repeat
end tell

Edit
The code isn’t ideal, I could have added delete range within the loop, and shift shift up (default behaviour), but it doesn’t seem necessary, so I leave it as it is, or up to any user to polish it.

#6

Sorry I missed the biggest part of my issue. Sorry!:smiley:

The Excel sheet I have is going to have rows with data and rows without data. I am trying to remove all of the empty rows in between the rows and stop at the end.
The word “Short” will always be in the last row.

Hope this helps.

#7

The script I posted should do exactly that, except for maybe selecting the cell at the end.

#8

Hello.

If it is so, that “short” is to be found in the last row, then I surmise that “short” is a unique value, and then you can just add the line below after the repeat loop:

 set FoundCell to (find range "A:A" what "short")
#9

I have got to be missing something so obvious it is not even funny here.

Here is the plan I have empty row in an Excel sheet, the last row contains the word “short”
I need it to cycle through and clear out all of the empty rows and stop when it gets to “short”

tell application "Microsoft Excel"
	activate
	select range "A1"
	set FoundResult to false
	
	set FoundCell to (find range "A:A" what "")
	set FoundResult to true
	delete range (entire row of FoundCell) shift shift up
	
	repeat while FoundResult is true
		set FoundCell to (find range "A:A" what "Short")
		set FoundResult to false
		try
			set FoundCell to (find range "A:A" what "")
			set first_Empty to first row index of FoundCell
			delete entire row of FoundCell
			set FoundProbe to (find range "A:A" what "*" after FoundCell)
			set first_full to first row index of FoundProbe
			if first_full < first_Empty then
				set FoundResult to false
				
			end if
		on error
			set FoundResult to false
			
		end try
	end repeat
	
end tell

I tried that and it cleans one row and stops which is better than the loop I was stuck in but…

#10

Hello.

I meant like this. :slight_smile:

tell application "Microsoft Excel"
	activate
	select range "A1"
	set FoundResult to false
	
	set FoundCell to (find range "A:A" what "")
	set FoundResult to true
	delete range (entire row of FoundCell) shift shift up
	
	repeat while FoundResult is true
		
		set FoundResult to false
		try
			set FoundCell to (find range "A:A" what "")
			set first_Empty to first row index of FoundCell
			delete entire row of FoundCell
			set FoundProbe to (find range "A:A" what "*" after FoundCell)
			set first_full to first row index of FoundProbe
			if first_full < first_Empty then
				set FoundResult to false
				
			end if
		on error
			set FoundResult to false
			
		end try
	end repeat
	set FoundCell to (find range "A:A" what "Short")
end tell
#11

Yeah I thought I was doing it wrong when I tried it that way because it is removing the first empty row it finds and then stops.

#12

Hello.

I am sorry for the misunderstanding I caused, I hope it works all right for you now. :slight_smile:

#13

Yes thank you for your help and kindness I did get it to work correctly.

#14

Hello.

I am glad it works for you! :slight_smile: