Delete problem 2

[UnsolvedCoding]

Hey all-

This one is stumping me.

I have a page where a summary of information is listed. When a cell in column N is highlighted by the user, the sub is to delete the row of information indicated by the highlighted cell.

Here's the problem - deleting the row of information with the highlighted cell will work to delete information if its not all together, if I run a handful of highlighted cells in consecutive rows it won't delete all the rows and I can't figure out why.

Any ideas?

Here's the entire code

Private Sub Clear_Highlighted()

' Just in case
On Error GoTo Error_Handler

Application.StatusBar = "Clearing highlighted rows"

i = 2

Do Until Trim(ThisWorkbook.Worksheets("2-Info").Cells(i, "G").Value) = "Grand Total:"

' Check if there is fill in the cell
If ThisWorkbook.Worksheets("2-Info").Cells(i, "N").Interior.ColorIndex <> xlNone Then

'Remove unwanted info
ThisWorkbook.Sheets("2-Info").Range("E" & i & ":N" & i).Delete Shift:=xlUp

End If

'Move down one row
i = i + 1

Loop

' Tell the user its done
Application.StatusBar = False

Exit Sub

Error_Handler:

Warning_Flag = 1

MsgBox Err.Description

Application.StatusBar = "Clear_Highlighted Failed. " & Err.Description

End Sub

 

[SkipVought]

Hi,

if I run a handful of highlighted cells in consecutive rows it won't delete all the rows

Think about what is happening, with painstaking step by step logic.

I could tell you, outright, but going thru the analysis yourself, will bring upi to a "Eureka!" moment.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Don't change 'i' in the loop. Cells that initially were in (i+1)-th row, after deleting cells in the i-th row will be moved to i-th row.

combo

 

[UnsolvedCoding]

Dang

 

[SkipVought]

The two general approches are 1) as combo suggested or 2) loop from the bottom up.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Because of various concerns with the overall application the loop now runs from the bottom up.

 

[SkipVought]

Bottom zup! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mscallisto]

It sounds like a great time for you to investigate Excels "Watch Window"

The Watch Window leads you to your "Eureka!" moment very quickly!!

Sam

 

[SkipVought]

faq707-4594

"Eureka!"

Isn't that how the guy began his berating of the garilc farmer? ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}