CODEWITHSUNDEEP
excelvba
Gaus Shaikh
Gaus Shaikh

Reputation: 115

Update the data in excel instead of deleting

The below code is deleting the records but I want to update the next column as "OK" instead of deleting entire row. Please advise as to what all changes are required.

Dim myFileNameDir As String
Dim ws1 As Worksheet
Dim iRow1 As Long
Dim str As String


myFileNameDir = "C:\Users\GShaikh\Desktop\Book16.xlsx"
Workbooks.Open Filename:=myFileNameDir, UpdateLinks:=0
Set ws1 = Worksheets("Students")


str = ListView1.SelectedItem.SubItems(1)

MsgBox str


    With ws1
      .AutoFilterMode = False
      iRow1 = .Range("B" & .Rows.Count).End(xlUp).row

          With .Range("B1:D" & iRow1)
              .AutoFilter Field:=1, Criteria1:="=*" & str & "*"
              .Offset(1, 0).SpecialCells    (xlCellTypeVisible).EntireRow.Delete
          End With

        .AutoFilterMode = False
    End With

ActiveWorkbook.Save
ActiveWorkbook.Close

Upvotes: 0

Views: 29

Answers (1)

Luuklag
Luuklag

Reputation: 3914

You would need to remove: (xlCellTypeVisible).EntireRow.Delete

And replace it with something like Cells.Value = "OK"

Upvotes: 1

Related Questions