Need help deleting half of the worksheet w/ MACRO

[bsmith27]

My dilemma. I have lots of worksheets and once i copy some of the data, the second half of the worksheet isn't necessary. There is a title row, but after that the information repeats. I know thats not a good description so here is an example:

<table x:str="" style="border-collapse: collapse; width: 466px; height: 198px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">LINE</td> <td style="width: 48pt;" width="64">VOLUME</td> <td style="width: 48pt;" width="64">FIRSTSP</td> <td style="width: 48pt;" width="64">LASTSP</td> <td style="width: 48pt;" width="64">FILENUM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">435B</td> <td>B10583</td> <td>21179</td> <td>21100</td> <td x:num="" align="right">42010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">435B</td> <td>B10583</td> <td>21099</td> <td>21017</td> <td x:num="" align="right">42093</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">407B</td> <td>B10583</td> <td>21021</td> <td>21100</td> <td x:num="" align="right">42284</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">407B</td> <td>B10583</td> <td>21101</td> <td>21183</td> <td x:num="" align="right">42367</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">407B</td> <td>B10583</td> <td>21184</td> <td>21239</td> <td x:num="" align="right">42450</td> </tr> <tr><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td></tr><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">435B</td> <td>B10584</td> <td>21179</td> <td>21100</td> <td x:num="" align="right">42010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">435B</td> <td>B10584</td> <td>21099</td> <td>21017</td> <td x:num="" align="right">42093</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">407B</td> <td>B10584</td> <td>21021</td> <td>21100</td> <td x:num="" align="right">42284</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">407B</td> <td>B10584</td> <td>21101</td> <td>21183</td> <td x:num="" align="right">42367</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">407B</td> <td>B10584</td> <td>21184</td> <td>21239</td> <td x:num="" align="right">42450</td> </tr> </tbody></table>
I used the gap to emphasize the second copy. The lengths of both copies are usually hundreds of lines long, but except for the "Volume" number, which is what I copy, the second copy can be deleted.

Using a macro, can I delete that second half, with it having a variable size?

I have tried using a find =address(match( formula and try to select to the end, but i couldn't get the value from a cell to be part of a variable range in a macro.

Thank you for your help.

 

[Fazza]

Hi, bs.

I've assumed that there is one header row and after that exactly half the rows are wanted. OK?

In case this is not what you wanted, please post again.

Suggest you back up the file before testing this!

Regards, Fazza

With Range("A1").CurrentRegion
  .Offset(.Rows.Count / 2 + 0.5).EntireRow.Delete
End With

 

[bsmith27]

Thanks, that worked flawlessly!!

 

[Fazza]

Great, bs.

Some explanation. I took a lazy approach with the code. It just offsets the 'current region' - the contiguous block around a cell - by enough rows so that when the deletion occurs the first half of the rows are unaffected. But all the unwanted rows and as many again further down are deleted. (It could easily be different if you want.)

So if you have one header rows and then 1000 rows to keep and another 1000 rows to delete (so from row 1002 to 2001), the routine will TOTALLY DELETE rows 1002 to 3001. That is, the NEXT 1000 also.

regards, F