Want to delete rows

[Guest]

I have text file which I import into Excel. There is a particular string
which I need to find (this can be in any row) so the row position is
variable. I can find this string no problem. What I want to do is once I find
the string, then starting from the row where the string is located, I want to
delete all rows above including the row in which the desired string is
located.

e.g the string is at Row 27, delete rows 1-27.

Thanks

 

[Shatin]

So you first find the string. Once the string is found, you press CTRL-SHIFT
and the UP ARROW keys. This will select all the cells in the column up to
the cell where the search string is in. Once you've done that, you choose
Edit>Delete>Entire row.

Or if what you mean is you have to repeat this process with many files, then
you use following VBA code:

Sub deleteRows()
Dim dString As String
Dim rng As Range
dString = InputBox("Please enter the search string.")
Set rng = ActiveSheet.UsedRange.Find(dString)
If Not rng Is Nothing Then
Range(rng, rng.End(xlUp)).EntireRow.Delete
Else
MsgBox ("Make sure you enter the correct string!")
End If
End Sub

 

[Dave Peterson]

Manually???

Just find that troublesome word
then hit ctrl-shift-home
then Edit|Delete...|Entire row

 

[Guest]

Thanks for the response. Doing it manually is trivial. What I wanted was a
VBA code which you have provided. I have used it in my Excel sheet but it
only deletes the enitre row which contains the string. I also want to delete
all rows above it. e.g if row 7 contains the string I want to delete rows 1-7

 

[Rowan]

Replace the line:

Range(rng, rng.End(xlUp)).EntireRow.Delete
with
Rows("1:" & rng.Row).Delete

Hope this helps
Rowan

 

[Guest]

One thing that I just noted is that your code stops at the first empty row it
encounters deleting upwards. For example the string is at row 7. Row 4 is an
empty row (no data). Your code will delete rows 5-7 but will stop at row 4. I
think it has some thing to do with the way you have constructed your If
statement.

Thanks
Farooq

 

[Guest]

It sure does help. Thanks.

Farooq

Replace the line:

Range(rng, rng.End(xlUp)).EntireRow.Delete
with
Rows("1:" & rng.Row).Delete

Hope this helps
Rowan