how to find an entry and delete it leaving the first intact

[caprichoo]

Hi, just a quick one

I have imported several text files into one worksheet. all the text files has a heading .. how do i create a macro so that it will find the second occurance of heading and then delete 4 rows with it?

the heading are all the same so i am thinking using a find function and search for the heading words and then delete all the subsequent occurance. i only have the idea but not sure how to implement it ... anyone can help ...Thanks a million

 

[al_b_cnu]

Hi,

This code deletes all subsequent rows (plus the following 3) from Sheet1 using A1 as the match string:

Sub DelHeads()
Dim rData As Range
Dim vRow As Variant, vHeadText As Variant
Dim WS As Worksheet

Set WS = Sheets("Sheet1")
vHeadText = WS.Range("A1").Text

Set rData = WS.Range("A5:A" & Rows.Count)
On Error Resume Next
vRow = WorksheetFunction.Match(vHeadText, rData, 0)
Do While IsNumeric(vRow)
    WS.Rows(vRow + 4 & ":" & vRow + 7).Delete shift:=xlUp
    Set rData = WS.Range("A5:A" & Rows.Count)
    vRow = "*"
    vRow = WorksheetFunction.Match(vHeadText, rData, 0)
Loop
End Sub

 

[caprichoo]

Thanks man.

You help a lot