Deleting Rows - a new issue

I know that there are numerous posts relating to deleting rows, I have read through them all but can't find a solution to this issue.

The source data of a spreadsheet that I am working with is formatted as a report (this can not be controlled within the source application). The problem with this is that it includes header details at each page break. I want to keep the first occurence of the header information which will always be in rows 1 - 5, but remove the rest along with associated blank lines.

The header detail includes unique text which will always be in column 'A', is it possible within Excel to locate this text string and delete the row that this text is in plus the four rows immediatelly above this? There will be several occurences of this text string within the spreadsheet.

Regards

iain

I'm relatively new to VBA as well, but see if this will work for you:

Please Login or Register  to view this content.

Jason,
Thanks for that, I'll give it a try and let you know.

Regards

Iain

Jason,
I know that this sounds rather dumb but I can't get this code to work, I keep getting syntax errors (my only VB knowledge is in the text book next to me!)

The text that I want to locate in column A is "SCC Job" and to then delete this and the four lines above it (there are several occurances of this text). Not sure if it makes a difference but this text is derived from a different worksheet using the LEFT function. If this causes a problem then I guess I could include in the macro a copy and paste special, values, into a different worksheet.

The spreadsheet is in columns A-M and the number of rows will vary but won't exceed 500.

I would be very greatfull if you would edit your code to include the specific spreadsheet data in the correct syntax.

Regards

Iain

Hi, Iain.

I'm not sure what type of data you are dealing with here, but if it is not confidential, can you post up an example so I can take a look at it?

I have a very similar formatted text file that is downloaded from a mainframe at my work, and this works fine to delete the rows.

Jason

Jason,
Thanks for that. The 'real' data is confidential but I will 'sanitize' a sample and post it.

Regards

Iain

Jason,
Data sample attached. Sheet 1 is the raw data, sheet 2 formats that data and sheet 3 is the finished report. The idea is to create this as a template so that raw data can be copied into sheet1 to automatically produce the formatted report.

The data includes two sections of headings (in reality there would be a lot more than this), there can also be a fifth data column.

Ultimatelly, as well as cleaning up the report, I am aiming to re-sort the work packages in order of the average package value.

Any assistance much appreciated.
SCC datar4.zip

Hi again, Iain.

There was a problem with the code, because I had to make an assumption. You stated that the found text, plus 4 rows above, needed to be deleted. By this statement, I assumed that the first header would also be 5 rows. Instead it is 3. Simple fix: just change all references from row 5 to row 3.

Please Login or Register  to view this content.

Jason,
Many thanks, I'll let you know how I get on.

Regards

Iain