Delete Xth Row

TommyPickles

New Member
Joined
Feb 16, 2005
Messages
32
Dear All,

I've searched through the baord but can't find the answer to a relatively simple problem.

I have an extremely long database (aroudn 11,000 rows), which is a blend of several database entries into one huge file. Each entry is 16 lines long, of whichincludes 4 lines of header rows on each entry that I dont need.

How do I make a macro that goes through and deletes the 4 lines of header rows on each entry?

Puzzled,
TommyPickles
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

How is your sheet actuallu structured?
4 rows of heading with 1 record?
 

TommyPickles

New Member
Joined
Feb 16, 2005
Messages
32
Thanks for your quick replies!

Each record has 5 rows of headers, followed by 18 lines of data. This repeats on and on for thousands of lines.

The first, second, fourth and fifth line of the record header is consistant, however the third changes (a generated number of the record).

So as I see it, there's either two possible solutions:
a) Macro that delets 5 rows, then scrolls forward 18 rows and deletes 5 rows again, ad infinitum

b) Macro that recognises RSBATCHCARDS in Column A (first header row), then proceeds to delete that line and subsequent 4 rows; ad infinitum

The link that you referred to shows how to delete header rows that are consistantly the same; which I could do for 4 of the rows but then it'd corrupt the database (as finding the generated number may get lost in the genuine entries...)
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

If you have something that is consistent, then you can find that item, and go a certain number of rows above, below and get the range to deleted. That is what the code in the link I gave is doing. The one line that is different is not an issue if you find one of the fixed lines (assuming that line 1,2,4and 5 are different...)


Tony
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
You can still use Find Method if the first row of header name is consistent.
try
Code:
sub test()
dim r as range, txt as string
txt="Your1stRowHeader" '<--- change
with activesheet
set r=.columns(1).find(txt)
if not r is nothing then
do
r.resize(5).entirerow.delete
set r=.columns(1).find(txt)
loop until r is nothing
end with
end sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,004
Messages
5,834,833
Members
430,324
Latest member
bosphoruskid

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
Top