combine macro

[sarfarazbutt]

Below macro is deleting empty row, I would like to have combine macro which can delete also the rows if text found in first cell "Not found" and top of this row also delete. i.e if row 40 found "Not found" text delete this row and 39 row should be delete. any idea




Option Explicit

Sub DeleteEmptyRows()

Dim LastRow As Long
Dim Cnt As Long
Dim r As Long

Application.ScreenUpdating = False

LastRow = Cells.Find(what:="*", _
after:=Range("A1"), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False).Row

For r = LastRow To 1 Step -1
If WorksheetFunction.CountA(Rows(r)) = 0 Then
Rows(r).Delete
Cnt = Cnt + 1
End If
Next r

Application.ScreenUpdating = True

MsgBox Cnt & " rows were deleted.", vbInformation

End Sub

 

[sarfarazbutt]

this code is deleting only empty row

Excel Workbook
	A	B	C	D
1			Nb. #	Room Type
2	Res N	Name	Company/Agency/Source/Group	Arrival
3	Cancellations For	Saturday, January 01, 2011
4	Not found
5	Cancellations For	Sunday, January 02, 2011
6	Not found
7	Cancellations For	Monday, January 03, 2011
8	Not found
9	Cancellations For	Tuesday, January 04, 2011
10	Not found
11	Cancellations For	Wednesday, January 05, 2011
12	Not found
13	Cancellations For	Thursday, January 06, 2011
14	Not found
15	Cancellations For	Friday, January 07, 2011
16	Not found
17	Cancellations For	Saturday, January 08, 2011
18	Not found
19	Cancellations For	Sunday, January 09, 2011
20	Not found
21	Cancellations For	Monday, January 10, 2011
22	194,499	Al Alfelasi, Nada	Booking.com	01/08/
23	Cancellations For	Tuesday, January 11, 2011
24	Not found
25	Cancellations For	Wednesday, January 12, 2011
26	Not found
27	Cancellations For	Thursday, January 13, 2011
28	Not found
29	Cancellations For	Friday, January 14, 2011
30	Not found
31	Cancellations For	Saturday, January 15, 2011
32	Not found
33	Cancellations For	Sunday, January 16, 2011
34	Not found
35	Cancellations For	Monday, January 17, 2011
MOEV_CXL_BOOKINGS.RPT

 

[AlphaFrog]

Try changing this line...
If LCase(Range("A" & r).Value) = "not found" Then

To this...
If LCase(Trim(Range("A" & r).Value)) = "not found" Then

 

[sarfarazbutt]

dear, same thing, only deleting empty rows

 

[AlphaFrog]

In the code, you kept "not found" all in lower case? It has to be all lower case in the code. It doesn't matter what the case the data is.

If LCase(Trim(Range("A" & r).Value)) = "[COLOR="Red"]not found[/COLOR]" Then

 

[sarfarazbutt]

you are great its work. thanks

 

[b.downey]

This should simplify thing a liittle. It will move thru the used range one row at a time and delete the row that meets the condition. If the row needs to be deleted, it will be, but the row counter wiil not move forward. This makes sure that multiple row in sequence that meet the condition will be deleted

Sub DeleteEmptyRows()
    Dim Cnt    As Long
    Dim r      As Long
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Set ws = ThisWorkbook.Worksheets(1)
    
    r = 2
    Do While r <= ws.UsedRange.Rows.Count
        If WorksheetFunction.CountA(Rows(r)) = 0 Or (LCase(Range("A" & r).Value) = "not found") Then
            Rows(r).Delete
            Cnt = Cnt + 1
        Else
            r = r + 1
        End If
    Loop
    Application.ScreenUpdating = True
    
    MsgBox Cnt & " rows were deleted.", vbInformation
    
End Sub

 

[Peter_SSs]

This should simplify thing a liittle. It will move thru the used range one row at a time and delete the row that meets the condition. If the row needs to be deleted, it will be, but the row counter wiil not move forward. This makes sure that multiple row in sequence that meet the condition will be deleted

Sub DeleteEmptyRows()
    Dim Cnt    As Long
    Dim r      As Long
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Set ws = ThisWorkbook.Worksheets(1)
    
    r = 2
    Do While r <= ws.UsedRange.Rows.Count
        If WorksheetFunction.CountA(Rows(r)) = 0 Or (LCase(Range("A" & r).Value) = "not found") Then
            Rows(r).Delete
            Cnt = Cnt + 1
        Else
            r = r + 1
        End If
    Loop
    Application.ScreenUpdating = True
    
    MsgBox Cnt & " rows were deleted.", vbInformation
    
End Sub

But does it produce the desired results? Run it on the sample data in post #9 and compare your results to the desired results shown in that post.

 

[b.downey]

You right Peter SSs. I missed the fact the the "Cancellation For:" rown needs to be deleted if the following row does not have a numeric number.

That's for pointing me back at post #9