excel - VBA Macro to delete unchecked rows using marlett check -

i don't have of background in vba, i'm trying create macro where, on push of button rows not have check mark in them in range deleted. browsed forums, , learned "marlett" check, character "a" in font displayed check mark. here code have generate "marlett check" automatically when clicking cell in column in appropriate range:

private sub worksheet_selectionchange(byval target range)      if target.cells.count > 1 exit sub         if not intersect(target, range("a10:a111")) nothing             target.font.name = "marlett"                 if target = vbnullstring                     target = "a"                 else                     target = vbnullstring                 end if         end if  end sub 

i have macro (assigned button) deletes rows without check mark in "a" column when button pressed:

sub delete_rows()  dim c range  on error resume next each c in range("a10:a111")     if c.value <> "a"         c.entirerow.delete     end if next c  end sub 

everything works, problem have press button multiple times before of unchecked rows deleted!! seems loop not working -- can please help??

thanks!

i think may due how you're deleting rows, might skipping row after every delete.

you might want change for-each regular loop. can control index you'r working on. see answer or other answers question see how it.

heres modified version should suit (possible) problem.

sub main()     dim row long     dim sheet worksheet     row = 10     set sheet = worksheets("sheet1")     application.screenupdating = false             if sheet.cells(row, 1).value = "a"             'sheet.rows(row).delete xlshiftup             row = row + 1         else             'row = row + 1             sheet.rows(row).delete xlshiftup         end if     loop while row <= 111     application.screenupdating = true end sub 

update try edit i've made if block, bit of guess. @ when have excel.

it go infinite loop regardless of suggested change. problem when got near end of data continually found empty rows (as theres no more data!) kept deleting them.

the code below should work though.

sub main()     dim row long: row = 10     dim count long: count = 0     dim sheet worksheet     set sheet = worksheets("sheet1")     application.screenupdating = false             if sheet.cells(row, 1).value = "a"             row = row + 1         else             count = count + 1             sheet.rows(row).delete xlshiftup         end if     loop while row <= 111 , row + count <= 111     application.screenupdating = true end sub