Nsryjdtyk

Hi I'm searching and trying to merge some VBA codes to overcome my problem but haven't been succesfull so far. What i want to do is search specific words in a Column (i.e. "XxX" this a just a part of that cell) and find the last match, and delete the rows below this row by using VBA. I have 278 txt files needs to be converted into xlsx for applying this in batch. I'm adding an example so maybe I'll explain better.

Ağ Teknolojileri Elemanı

Network Technologies Personnel

(Level 5)

12UY0046-5/A1

Occupational Health and Safety, Quality, Work Organization and Professional Development

XxX Explains OHS measures.

XxX Explains environment protection measures.

XxX Defines quality applications.

XxX Explains how to make work organization.

XxX Defines activities needed to be carried out for professional development.

12UY0046-5/A2

Basis of Computer Hardware and Software

XxX Explains the operating logic of the computer.

XxX Explains basic computer use.

XxX Explains the working principles of computer energy hardwares.

XxX Describes the basic features of computer components.

df

fsd

s

gfd

gdfg

dfs

fd

Note: All in "A" column.

For this sample I want the VBA code will find the last cell that contains "XxX" (XxX Describes the basic features of computer components.) and delete all the rows below this row.

I don't know if i can do this to txt files. But nevertheless i couldn't find a way to do it for txt files, i need to change those txt files into xlsx.

Thanks.

Try:

Option Explicit

Sub delAfter()

    Const sFind As String = "XxX"

    Dim WS As Worksheet, R As Range



'Find last sFind

Set WS = Worksheets("sheet2") 'or ActiveSheet or whatever

With WS.Cells

    Set R = .Find(what:=sFind, _

         after:=.Item(1, 1), _

         LookIn:=xlValues, _

         lookat:=xlPart, _

         searchorder:=xlByRows, _

         searchdirection:=xlPrevious, _

         MatchCase:=True)

    If Not R Is Nothing Then

        Set R = WS.Range(R.Offset(1, 0), .Cells(.Rows.Count, R.Column).End(xlUp))

        R.EntireRow.Delete 'deletes entire row.  If just want a single column, adjust

    End If

End With



End Sub

Will delete entire rows after finding last XxX.
If you examine the code, you will see we start in the first row, but the searchorder is xlprevious so we will be searching from the bottom.

If you need to restrict it to a specific column, just change the range being searched.

Ok,

This code should work for strings containing XxX in columns a

Option Explicit



Sub Delete()



Dim wb As Workbook

Dim ws As Worksheet

Dim Lastrow As Long, i As Long, RowNum As Long

Dim str As String

Dim r As Range, Cell As Range



Set wb = ThisWorkbook

Set ws = wb.Sheets("Blad1")



str = "XxX"



Lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Set r = ws.Range(ws.Cells(1, 1), ws.Cells(Lastrow, 1))



For Each Cell In r

    If Cell.Value Like "*" & str & "*" Then

        i = Cell.Row

        Exit For

    End If

Next Cell



For RowNum = i + 1 To Lastrow

    ws.Rows(i + 1).Delete

Next RowNum





End Sub