Cleaning forms

  • Thread starter Lost in Microbiology
  • Start date
L

Lost in Microbiology

I have an imported text file from a lab system that has a header every 51 rows.

I would like to create a macro that deletes 6 rows every 51 rows, but can't
figure it out, please help. Thanks.
 
Z

Zone

You mean there are 6 heading rows in every 51 rows? Are the heading rows
the same distance apart throughout the file? Is there anything unique about
the heading rows (such as text in a column that normally has numbers)?
 
L

Lost in Microbiology

Zone,

Correct, every at row 52 there are 6 consecutive rows that need to be
deleted. They have a title, the first column always starts out "REPORT
TYPE...."
The heading rows are the same distance throughout.

Thanks for your help
 
D

Dave Peterson

If "Report Type" doesn't appear in any other cell except that header, then you
could use:

Option Explicit
Sub testme()

Dim myStr As String
Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

'* means there could be text following
'report type
myStr = "report type*"

With wks.Range("a:A")
Do
Set FoundCell = .Cells.Find(what:=myStr, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'no more to fix
Exit Do
Else
FoundCell.Resize(6, 1).EntireRow.Delete
End If
Loop
End With
End Sub
 
Top