Loop Depending On Search Result.

[dager6]

Hi,

I am trying to improve my code for find and remove the empty row below the cells that contain "12-2014".

Unfortunately, I could not figure this out. Your help will be deeply appreciated.

Cells.Find(What:="12-2014", After:=ActiveCell, LookIn:=xlFormulas, _        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _        MatchCase:=False, SearchFormat:=False).Activate        If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp            Cells.FindNext(After:=ActiveCell).Activate        If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp            Cells.FindNext(After:=ActiveCell).Activate        If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp            Cells.FindNext(After:=ActiveCell).Activate        If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp            Cells.FindNext(After:=ActiveCell).Activate        If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp            Cells.FindNext(After:=ActiveCell).Activate        If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp            Cells.FindNext(After:=ActiveCell).Activate        If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp            Cells.FindNext(After:=ActiveCell).Activate        If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp            Cells.FindNext(After:=ActiveCell).Activate            If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp            Cells.FindNext(After:=ActiveCell).Activate        If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp            Cells.FindNext(After:=ActiveCell).Activate        If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp            Cells.FindNext(After:=ActiveCell).Activate        If ActiveCell.Offset(1, 1) = "" Then ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp

Best,

Matt

 

[venkat1926]

if you want to delete the entire row go from bottom row to top row with a loop
can you try to write the macro now.

 

[dager6]

Not all the empty rows. Just the ones below "12-2014". The search function is from top down. My code does the search 12 times and delete all the rows below the ones that has "12-2014". I want to have a better code to only delete the empty rows no matter how many times "12-2014" appears.

Thanks for your reply.

Best,

Matt

 

[dager6]

Please find my updated Code below. The problem is that it deletes rows contain "12-2014" for 16 times no matter the deleted rows are empty or not.

Dim n As Long
    n = 1
    
    Application.ScreenUpdating = False
    
    Range("A1").Select
    
    Cells.Find(What:="12-2014", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    
    For n = 1 To 16
    
        If ActiveCell.Offset(1, 1) = vbNullSting Then
            n = n + 1
            ActiveCell.Offset(1, 1).EntireRow.Delete Shift:=xlUp
            Cells.FindNext(After:=ActiveCell).Activate
        Else
            n = 16
        End If
    
    Next
            
    Application.ScreenUpdating = True

How can I fix this?

Thanks,

Matt

 

[dager6]

The cell below was not empty. It has a space, which makes it looks empty.

Thanks for all the help.