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

caprichoo

Board Regular
Joined
Nov 2, 2005
Messages
53
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
Hi,

This code deletes all subsequent rows (plus the following 3) from Sheet1 using A1 as the match string:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,387
Members
412,590
Latest member
Velly
Top