Hi Experts,
I am looking for a macro that will do the thngs mentioned below
If column I of 1.xls matches with column B of H2.xlsb then clear all the data in that row from 3rd column
Macro will be placed in H2.xlsb
Thnx For the help
Hi Experts,
I am looking for a macro that will do the thngs mentioned below
If column I of 1.xls matches with column B of H2.xlsb then clear all the data in that row from 3rd column
Macro will be placed in H2.xlsb
Thnx For the help
Try:
![]()
Sub ClearRange() Application.ScreenUpdating = False Dim Val As String, ws1 As Worksheet, ws2 As Worksheet, i As Long, v1, v2, RngList As Object, lCol As Long Set ws1 = ThisWorkbook.Sheets("Sheet1") Set ws2 = Workbooks("1sample.xlsx").Sheets("Sheet1") v1 = ws1.Range("B2", ws1.Range("B" & Rows.Count).End(xlUp)).Value v2 = ws2.Range("I2", ws2.Range("I" & Rows.Count).End(xlUp)).Resize(, 10).Value Set RngList = CreateObject("Scripting.Dictionary") For i = 1 To UBound(v2, 1) Val = v2(i, 1) If Not RngList.Exists(Val) Then RngList.Add Val, Nothing End If Next i For i = 1 To UBound(v1, 1) Val = v1(i, 1) If RngList.Exists(Val) Then With ws1 lCol = .Cells(i + 1, .Columns.Count).End(xlToLeft).Column If lCol > 2 Then .Range("C" & i + 1).Resize(, lCol - 2).ClearContents End If End With End If Next i Application.ScreenUpdating = True End Sub
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
Thnx Alot Mumps Sir for helping me in solving this problem
Have a Great Day
You are very welcome.![]()
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks