Please help me to delete the particular data from huge data list

[sampath]

Hello,

I have huge data in excel and i need to delete the particular data from that. Already got the excel script for deleting that "SE" from huge data. when i run that script, all the data was deleted in SE contained cell.

Need to modify script from my below request.

Sub RemoveDataAfterGrp() 
Dim iCol, iRow As Integer
iCol = 1
iRow = 3
Do While Sheet1.Cells(iRow, iCol) <> ""
  If VBA.InStr(1, Sheet1.Cells(iRow, iCol).Value, "Group", vbTextCompare) > 0 Then
  Do
  Sheet1.Cells(iRow, iCol).Value = ""
  iRow = iRow + 1
  Loop Until Sheet1.Cells(iRow, iCol).Value = ""
  Else
  iRow = iRow + 1

  End If
  If Sheet1.Cells(iRow, iCol) = "" Then
  iCol = iCol + 1
  iRow = 3
  End If
   Loop
End Sub

My request
1.To delete "SE" and after data in "SE" contained cell and after cell of "SE" contained cell.

Additional Requirement
2. Delete the "N°"
3. Delete 2nd row.

Please help me to do this things.

Regards,
Sampath.S

 

[Hui]

You were very close

I think this will do it for you

Sub RemoveDataAfterGrp()
Dim iCol As Integer
Dim iRow As Integer
iCol = 1
iRow = 3
Do While Sheet1.Cells(iRow, iCol) <> ""
  If InStr(1, Sheet1.Cells(iRow, iCol).Value, "SE") > 0 Then
  If Left(Sheet1.Cells(iRow, iCol).Value, 2) = "SE" Then
  Do
  Sheet1.Cells(iRow, iCol).Value = ""
  iRow = iRow + 1
  Loop Until Sheet1.Cells(iRow, iCol).Value = ""
  Else
  Sheet1.Cells(iRow, iCol).Value = Mid(Sheet1.Cells(iRow, iCol).Value, InStr(1, Sheet1.Cells(iRow, iCol).Value, "SE"), 999)
  iRow = iRow + 1
  Do
  Sheet1.Cells(iRow, iCol).Value = ""
  iRow = iRow + 1
  Loop Until Sheet1.Cells(iRow, iCol).Value = ""

  End If
  Else
  iRow = iRow + 1

  End If
  If Sheet1.Cells(iRow, iCol) = "" Then
  iCol = iCol + 1
  iRow = 3
  End If
Loop
Rows("2:2").Delete Shift:=xlUp
End Sub

or see attached

 

[sampath]

Hi Hui,

Thanks for your reply,

In Presentation1.xlsm, Column E & I does not delete the "SE" and before data of SE only deleted when ran this macro.

I need to delete only SE and after cells of SE. kindly provide the solution.

Thanks and regareds,
Sampath.S

 

[Hui]

Try the following code

Sub RemoveDataAfterGrp()
Dim iCol As Integer
Dim iRow As Integer
iCol = 1
iRow = 3
Do While Sheet1.Cells(iRow, iCol) <> ""
  If InStr(1, Sheet1.Cells(iRow, iCol).Value, "SE") > 0 Then
  Do
  Sheet1.Cells(iRow, iCol).Value = ""
  iRow = iRow + 1
  Loop Until Sheet1.Cells(iRow, iCol).Value = ""
  Else
  iRow = iRow + 1
  End If
 
  If Sheet1.Cells(iRow, iCol) = "" Then
  iCol = iCol + 1
  iRow = 3
  End If
Loop
Rows("2:2").Delete Shift:=xlUp
End Sub

If this isn't right can you please supply a picture of what the final result should be