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

 

[b.downey]

Try replacing the following line of your code:

If (WorksheetFunction.CountA(Rows(r)) = 0) Then

with

If (WorksheetFunction.CountA(Rows(r)) = 0) Or (Cells(r, "A") = "Not found") Then

 

[sarfarazbutt]

Great, that's make half work done, but still small issue when text found "not found" should be delete two rows
i.e. delete the row if text found "not found" and one row top of this text, I mean if row no 40 has text "not found" then delete the two rows one 40 and and 39.

 

[AlphaFrog]

    For r = LastRow To 1 Step -1
        If WorksheetFunction.CountA(Rows(r)) = 0 Then
            Rows(r).Delete
            Cnt = Cnt + 1
        ElseIf Range("A" & r).Value = "Not Found" Then
            Rows(r - 1).Resize(2).Delete
            Cnt = Cnt + 2
        End If
    Next r

 

[sarfarazbutt]

Still small issue, see the below screen shoot

before run the macro

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
16
17			Nb. #	Room Type	Actual Rate
18	Res N	Name	Company/Agency/Source/Group	Arrival	Depart.	RN	Ad/Chd	Rate Code	Segment	Source	Allocation	Cxl Reason
19
20
21
22	Cancellations For	##########
23
24
25
26	Not Found
27
28	Cancellations For	##########
29
30
31
32	Not found
33
34	Cancellations For	##########
35
36
37
38	Not found
39
40	Cancellations For	##########
41
42
43
44	Not found
MOEV_CXL_BOOKINGS.RPT

after run the macro

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
7	Res N	Name	Company/Agency/Source/Group	Arrival	Depart.	RN	Ad/Chd	Rate Code	Segment	Source	Allocation	Cxl Reason	Booker
8	Cancellations For	##########
9	Cancellations For	##########
10	Cancellations For	##########
11	Cancellations For	##########
12	Cancellations For	##########
13	Cancellations For	##########
14	Cancellations For	##########
15	Cancellations For	##########
16	Cancellations For	##########
17	Cancellations For	##########
18	194,499	Al Alfelasi, Nada	Booking.com	01/08/	03/08/	2	4/0/0/0/0	1	SE3KC0	FLEXPA1B	0.00	LBR BAR on leisure, incl.merc	IAB Ind. Private Booking
19	Cancellations For	##########
20	Cancellations For	##########
21	Cancellations For	##########
22	Cancellations For	##########
23	Cancellations For	##########
24	Cancellations For	##########
25	Cancellations For	##########
26	Cancellations For	##########
27	Cancellations For	##########
28	Cancellations For	##########
29	Cancellations For	##########
30	Cancellations For	##########
MOEV_CXL_BOOKINGS.RPT

 

[Peter_SSs]

Is the problem just that some rows above row 18 have been deleted?

If so,

For r = LastRow To 19 Step -1

 

[AlphaFrog]

What's the "small issue"? Do you want the "Cancellations For" rows deleted as well?

    For r = LastRow To 1 Step -1
        If WorksheetFunction.CountA(Rows(r)) = 0 Or [COLOR="Red"]Range("A" & r).Value = "Cancellations For"[/COLOR] Then
            Rows(r).Delete
            Cnt = Cnt + 1
        ElseIf Range("A" & r).Value = "Not Found" Then
            Rows(r - 1).Resize(2).Delete
            Cnt = Cnt + 2
        End If
    Next r

 

[sarfarazbutt]

the issue is if text "not found" then above row "Cancellations For" should delete, but not any row delete if "not found" text is not in first cell. i.e.

if row 3 text will be "not found" then above row (which is 2) "Cancellations For" should delete, but if any other text/number found above row should not delete.

i believe first loop should delete the empty rows in sheet than above statement applied.

 

[sarfarazbutt]

May be below screen shoot will be understand more

below is export sheet in orginal condition

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1
2
3	Cancellations For
4
5
6	Not found
7
8
9
10
11	Cancellations For
12	123545
13
14	Cancellations For
15	Not found
16
17	Cancellations For
18	52412
19
20	Cancellations For
21
22
23	Not found
24
Sheet1

Below is required format

Excel Workbook
	A	B
1
2
3	Cancellations For
4	123545
5	Cancellations For
6	52412
Sheet1

 

[AlphaFrog]

Sub DeleteEmptyRows()
    
    Dim LastRow As Long
    Dim Cnt    As Long
    Dim r      As Long
    
    Application.ScreenUpdating = False
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For r = LastRow To 3 Step -1
        If WorksheetFunction.CountA(Rows(r)) = 0 Then
            Rows(r).Delete
            Cnt = Cnt + 1
        End If
    Next r
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For r = LastRow To 3 Step -1
        If LCase(Range("A" & r).Value) = "not found" Then
            Rows(r - 1).Resize(2).Delete
            Cnt = Cnt + 2
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox Cnt & " rows were deleted.", vbInformation
    
End Sub