Using Loop in VBA to Delete a repeating selection of Rows

E

EverClear

New Member
Joined
Oct 23, 2012
Messages
32
Hi there!!

I have a “soft copy” of an 80-page report that was downloaded from an older mainframe system. The soft copy was in a .txt format, and I imported it into Excel using fixed-width delimiters for the columns. Since the report is 80-pages, it translates to more than 1,700 rows of data in Excel. An extremely truncated version of the report appears below as a sample (it looks a bit weird b/c it's difficult to get the formatting just right in this post)…</SPAN>

As shown below, the companies listed on the report are grouped into “associations” that have unique association id numbers, and have separate sections. At the top of each page of the report, there’s a report header (title of the report, the date, page#, etc.). Please note: This is NOT a *page* header (as in page setup). Rather, when the mainframe report is imported into Excel, the report header appears on rows in the spreadsheet itself. Once the report is imported into Excel, this header is repeated, and takes up the first 9 rows of each association's section (no more, no less).</SPAN>

My dilemma – I want to DELETE the headers from the report, leaving only the company data. Instead of manually going through 1,700+ rows of data, I need to find a way with <ACRONYM>vba</SPAN></ACRONYM> code to DELETE the first 9 rows of each section. I thought I could search for a specific character string in Row 1 of the header, delete that row and the next 8 rows, then jump to the next instance of that character string. </SPAN>

Here’s the code I have so far:

Sub Test()</SPAN>
Const sWORD As String = "ABC-DEFGHA"</SPAN>
Dim gotit As Range</SPAN>

On Error Resume Next</SPAN>

Range("B1").Activate</SPAN>

Set gotit = ActiveSheet.Cells.Find( _</SPAN>
what:=sWORD, _</SPAN>
LookIn:=xlValues, _</SPAN>
LookAt:=xlPart, _</SPAN>
MatchCase:=False)</SPAN>

If Not gotit Is Nothing Then</SPAN>
Do</SPAN>
gotit.EntireRow.Select</SPAN>
For i = 1 To 8</SPAN>
Selection.EntireRow.Delete</SPAN>
Next i</SPAN>
Set gotit = ActiveSheet.Cells.FindNext</SPAN>
Loop Until gotit Is Nothing</SPAN>
End If</SPAN>

End Sub</SPAN>

The problem is – it finds and deletes the first row, then it continues deleting rows, INCLUDING MY DATA!!


[TABLE="width: 901"]
<TBODY>[TR]
[TD]

REPORT #ABC-DEFGHA</SPAN>

<TBODY>
</TBODY>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]REPOR</SPAN>
[/TD]
[TD]T NAME</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]REPORT DATE</SPAN>
[/TD]
[TD]
PAGE 1 </SPAN>

<TBODY>
</TBODY>
[/TD]
[/TR]
[TR]
[TD]JOB #</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]REPOR</SPAN>
[/TD]
[TD]T HEADER</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]REGION : REGION NAME</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]SUMMARY</SPAN>
[/TD]
[TD]FO</SPAN>
[/TD]
[TD]R CURR</SPAN>
[/TD]
[TD]ENT MONTH</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]ASSOCIATION : 1234567890</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]COMPANY</SPAN>
[/TD]
[TD="colspan: 2"]COMPANY</SPAN>
[/TD]
[TD]
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]DOLLAR</SPAN>
[/TD]
[TD]NET</SPAN>
[/TD]
[/TR]
[TR]
[TD]NAME</SPAN>
[/TD]
[TD]NO.</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]INVOICED</SPAN>
[/TD]
[TD]
[/TD]
[TD]EXCESS</SPAN>
[/TD]
[TD]ADJUSTMENT</SPAN>
[/TD]
[TD]INVOICED</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY A1</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]157</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]40,260.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]40,260.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY A2</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]150</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]17,450.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]17,450.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY A3</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]118</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]10,002.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]10,002.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY A4</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]107</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]20,792.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]20,792.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY A5</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]148</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]9,427.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]9,427.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]ASSOCIATION TOTAL</SPAN>
[/TD]
[TD][/TD]
[TD]680</SPAN>
[/TD]
[TD][/TD]
[TD]97,931.00</SPAN>
[/TD]
[TD][/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]97,931.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]
ASSOCIATION TOTAL</SPAN>

<TBODY>
</TBODY>
[/TD]
[TD]TOTAL DEBI</SPAN>
[/TD]
[TD]TS=</SPAN>
[/TD]
[TD]121 TOT</SPAN>
[/TD]
[TD]AL CREDITS=</SPAN>
[/TD]
[TD]0 NET F</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]

<TBODY>
</TBODY>
[/TD]
[/TR]
[TR]
[TD]REPORT #ABC-DEFGHA</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]REPOR</SPAN>
[/TD]
[TD]T NAME</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]REPORT DATE</SPAN>
[/TD]
[TD]PAGE 2</SPAN>
[/TD]
[/TR]
[TR]
[TD]JOB #</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]REPOR</SPAN>
[/TD]
[TD]T HEADER</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]REGION : REGION NAME</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]SUMMARY</SPAN>
[/TD]
[TD]FO</SPAN>
[/TD]
[TD]R CURR</SPAN>
[/TD]
[TD]ENT MONTH</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]ASSOCIATION : 2345678901</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]COMPANY</SPAN>
[/TD]
[TD="colspan: 2"]COMPANY</SPAN>
[/TD]
[TD]
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]DOLLAR</SPAN>
[/TD]
[TD]NET</SPAN>
[/TD]
[/TR]
[TR]
[TD]NAME</SPAN>
[/TD]
[TD]NO.</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]INVOICED</SPAN>
[/TD]
[TD]
[/TD]
[TD]EXCESS</SPAN>
[/TD]
[TD]ADJUSTMENT</SPAN>
[/TD]
[TD]INVOICED</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B6</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]228</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]24,558.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]24,558.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B7</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]86</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]31,257.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]31,257.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B8</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]135</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]33,922.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]33,922.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B9</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]85</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]5,807.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]5,807.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B10</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]149</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]49,828.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]49,828.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B11</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]121</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]30,245.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]30,245.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B12</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]33</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]23,988.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]23,988.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B13</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]127</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]39,978.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]39,978.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B14</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]227</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]32,909.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]32,909.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B15</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]44</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]48,601.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]48,601.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B16</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]127</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]47,286.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]47,286.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]ASSOCIATION TOTAL</SPAN>
[/TD]
[TD][/TD]
[TD]1362</SPAN>
[/TD]
[TD][/TD]
[TD]368,379.00</SPAN>
[/TD]
[TD][/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]368,379.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]ASSOCIATION TOTAL</SPAN>
[/TD]
[TD]TOTAL DEBI</SPAN>
[/TD]
[TD]TS=</SPAN>
[/TD]
[TD]397 TOT</SPAN>
[/TD]
[TD]AL CREDITS=</SPAN>
[/TD]
[TD]0 NET F</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]REPORT #ABC-DEFGHA</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]REPOR</SPAN>
[/TD]
[TD]T NAME</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]REPORT DATE</SPAN>
[/TD]
[TD]
PAGE 3</SPAN>

<TBODY>
</TBODY>
[/TD]
[/TR]
[TR]
[TD]JOB #</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]REPOR</SPAN>
[/TD]
[TD]T HEADER</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
REGION : REGION NAME</SPAN>

<TBODY>
</TBODY>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]SUMMARY</SPAN>
[/TD]
[TD]FO</SPAN>
[/TD]
[TD]R CURR</SPAN>
[/TD]
[TD]ENT MONTH</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]ASSOCIATION : 3456789012</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]COMPANY</SPAN>
[/TD]
[TD="colspan: 2"]COMPANY</SPAN>
[/TD]
[TD]
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]DOLLAR</SPAN>
[/TD]
[TD]NET</SPAN>
[/TD]
[/TR]
[TR]
[TD]NAME</SPAN>
[/TD]
[TD]NO.</SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]INVOICED</SPAN>
[/TD]
[TD]
[/TD]
[TD]EXCESS</SPAN>
[/TD]
[TD]ADJUSTMENT</SPAN>
[/TD]
[TD]INVOICED</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z18</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]95</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]36,247.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]36,247.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z19</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]17</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]24,776.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]24,776.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z20</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]151</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]18,758.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]18,758.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z21</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]239</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]9,551.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]9,551.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z22</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]161</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]17,891.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]17,891.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z23</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]90</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]34,873.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]34,873.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z24</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]164</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]6,586.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]6,586.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z25</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]20</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]49,028.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]49,028.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z26</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]10,636.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]10,636.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z27</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]39</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]31,243.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]31,243.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z28</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]174</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]6,001.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]6,001.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z29</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]224</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]38,457.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]38,457.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z30</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]150</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]36,650.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]36,650.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z31</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]246</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]8,539.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]8,539.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z32</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]17,671.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]17,671.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z33</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]25</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]8,765.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]8,765.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z34</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]242</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]18,074.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]18,074.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z35</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]95</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]48,007.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]48,007.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z36</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]208</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]47,987.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]47,987.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z37</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]102</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]13,941.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]13,941.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z38</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]197</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]38,485.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]38,485.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z39</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]181</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]25,144.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]25,144.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY Z40</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD]245</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]41,686.00</SPAN>
[/TD]
[TD]99,999</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD][/TD]
[TD]41,686.00</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


</SPAN>
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Try modifying your code to:

Code: 
Sub Test()
Const sWORD As String = "ABC-DEFGHA"
Dim gotit As Range

On Error Resume Next

Set gotit = ActiveSheet.Cells.Find( _
what:=sWORD, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)

Do While Not gotit Is Nothing
    gotit.Resize(8, 1).EntireRow.Delete
    Set gotit = ActiveSheet.Cells.FindNext
Loop
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel VBA inconsistent errors when trying to copy and paste objects from Excel to Word
2
Replies
18
Views
36K
Jon Peltier
J
VBA learner ITG
  • Question Question
Advice how i would tackle this using an excel formula or vba solution
Replies
1
Views
574
ImranMalek
I
E
  • Question Question
Excel vba: Automated Search/Copy/Paste
Replies
2
Views
1K
EverClear
E
S
  • Question Question
Automatically downloading data (multiple excel files at one shot) from website into a folder and splitting the data into multiple worksheets using Exc
Replies
2
Views
5K
srimani202002
S
D
  • Question Question
Help with using visual basic to create dynamic links to other excel files
Replies
2
Views
611
domiereavron
D

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top