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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,388
Messages
5,571,847
Members
412,420
Latest member
grace_abar
Top