vba to delete rows until spesific cell is equal to certain text

[Alwinaz]

I need a vba that will delete rows until cell A10="Transaction List"

Screen shot

<TABLE style="WIDTH: 311pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=414><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 23pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=30>A9</TD><!--td> Code:</td--><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64 align=right>7785</TD><!--<td><input name=txtStreetCode class=cNormalText maxlength=5 size=5 value="12345"></td>--><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>A10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>A11</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: midnightblue; WIDTH: 288pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=384 colSpan=6>Transaction List (Bank View) for policy 548776</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Bank Date</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Item</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Trans Type</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Amount</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Reason</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" id=DivAllocHeader class=xl65 width=64>Allocated</TD></TR></TBODY></TABLE>

Thus the vba must delete rows starting from row A10 until it find the text "Transaction List". the policy number in this row will always differ, but will always start with the text "Transaction List....."

Can anybody help me?ray:

 

[Firefly2012]

Hi

perhaps:

Dim r As Range
 
Set r = Range("A10","A" & Rows.Count).Find(What:="Transaction List",LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext,MatchCase:=False)
 
If Not r Is Nothing Then 
  Range("A10:A" & r.Row).EntireRow.Delete
Else
  MsgBox "No cell matching criteria found!"
End If

 

[Alwinaz]

Thanks for your quick response. Nothing is happening. I have posted it in the "view Code" but nothing is happening. Am i doning something wrong?

 

[Firefly2012]

Sorry, should have made clear: it needs to go between Sub () End Sub statements in a standard module in te Visual Basic Editor.

With he relevant sheet visible in Excel, open up the VBE with Alt+F11.

Insert a standard module with Insert>Module

Paste the following into the code module window which will open:

Sub DelRows()
Dim r As Range
 
Set r = Range("A10","A" & Rows.Count).Find(What:="Transaction List",LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext,MatchCase:=False)
 
If Not r Is Nothing Then 
  Range("A10:A" & r.Row - 1).EntireRow.Delete
Else
  MsgBox "No cell matching criteria found!"
End If
End Sub

With the cursor anywhere within the above code, hit F5 to run the macro.


EDIT: note there was an error in the first code such that it would delete the Transaction List row too! Now corrected...

 

[Alwinaz]

I get the error message "No cell matching criteria found!". It seems as if it does not find the text part "Transaction List".

 

[Firefly2012]

Is the cell that contains Transaction List part of a merged cell by any chance?

 

[Alwinaz]

just checked. Yes it is part of a merged cell.

 

[Alwinaz]

I managed to fix this. But it still delete the row even if "Transaction List" is in A10. can you help me please!!!!!

 

[Firefly2012]

Sure - change code to this:

Sub DelRows()
Dim r As Range
 
Set r = Range("A10","A" & Rows.Count).Find(What:="Transaction List",LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext,MatchCase:=False)
 
If Not r Is Nothing Then
  If r.Row > 10 Then Range("A10:A" & r.Row - 1).EntireRow.Delete
Else
  MsgBox "No cell matching criteria found!"
End If
End Sub

 

[Alwinaz]

thank you very much! it works like a bomb!