Clear the data in the row if condition match

[zyxw1234]

I have a Macro, but it has errors mentioned below
I need to clear all the data from column C But this macro clear all the data in that row(I don't want the macro to clear the column A & column B data)

Code: Select all

Sub STEP10()
    
    Dim wb1 As Workbook, wb2 As Workbook
    Dim rg1 As Range, rg2 As Range
    Dim i As Long, c As Range
    Set wb1 = Workbooks.Open("C:UsersWolfieeeStyleDesktop1.xls")
    Set rg1 = wb1.Worksheets(1).Cells(1, 1).CurrentRegion
    Set wb2 = Workbooks.Open("C:UsersWolfieeeStyleDesktop2.xlsx")
    Set rg2 = wb2.Worksheets(1).Cells(1, 1).CurrentRegion
    
    For i = 2 To rg1.Rows.Count
        If rg1(i, 19).Value Like "*[0-9]*" Then
            Set c = rg2.Columns(2).Find(rg1(i, 25).Value)
            If Not c Is Nothing Then c.EntireRow.ClearContents
        End If
    Next i
    wb2.Save
    wb2.Close
    wb1.Close
    
End Sub

Download file link
https://drive.google.com/file/d/1sMJWSZ ... sp=sharing
https://drive.google.com/file/d/1Ut5WgZ ... sp=sharing

[HansV]

If you don't want to clear the entire row, you obviously shouldn't use c.EntireRow.ClearContents

As Leif has already asked, please attach the workbooks to your post instead of posting a link to Google Drive.

[zyxw1234]

Sure HansV Sir I attached the file & from now onwards I will attach the file in the post itself, Actually there was a issue & I was unable to upload the files but now I can upload it

[zyxw1234]

I know I don't have to use this c.EntireRow.ClearContents
I can use .cells.clear but after that also some arrangements is required & I tried & I was unable to do the same HansV Sir

[HansV]

Since you don't want to clear the entire row but only the cell in column C, you'll have to replace EntireRow with a reference to the cell in column C.

[zyxw1234]

Yes but that only I am unable to do

[Leif]

Yes but that only I am unable to do

Do you know how to record a macro?
If not, see Automate tasks with the Macro Recorder - Office Support

If/when you do, simply record a macro where you delete the contents of the cell.

Use the contents of this macro to work out how to select a single cell and clear its content.

Again, and for the final time, if you are unwilling to help yourself, we are not willing to help you, and will certainly not do your work for you.

[zyxw1234]

No issues Fine
Thnx Alot for ur Great Help HansV Sir & Leif Sir

[zyxw1234]

Code: Select all

Sub Macro()

Dim Wb1 As Workbook, Wb2 As Workbook, a, Dic As Object
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Dic = CreateObject("scripting.dictionary")

Set Wb1 = Workbooks.Open("C:UsersWolfieeeStyleDesktopsample1.xls")
Set Wb2 = Workbooks.Open("C:UsersWolfieeeStyleDesktopsample2.xlsx")

Set Ws1 = Wb1.Worksheets(1)
Set Ws2 = Wb2.Worksheets(1)

a = Ws1.Range("A1").CurrentRegion
Wb1.Close False

For x = 1 To UBound(a)
  If Not Dic.exists(a(x, 25)) And a(x, 19) > 0 And IsNumeric(a(x, 19)) Then Dic.Add a(x, 25), Nothing
Next

For x = 2 To Ws2.Range("A" & Rows.Count).End(xlUp).Row
  If Dic.exists(Ws2.Cells(x, "B").Value) Then Range(Ws2.Cells(x, 3), Ws2.Cells(x, Ws2.UsedRange.Columns.Count)).ClearContents
Next

Wb2.Close True

End Sub

Problem Solved Thnx Alot Sir