Please check hope this is what you are looking for:
Option Explicit
Sub RemoveData()
Dim wbkFile As Workbook
Dim vararrDataToRemove() As Variant
Dim lngLoop As Long
Dim rngData As Range
Dim rngTemp As Range
'Const variable change accordingly as per your need like path and sheet
name
Const strDataFilePath As String =
"D:\Users\llm82928\Desktop\example file.xlsx"
Const strCompareFilePath As String =
"D:\Users\llm82928\Desktop\SLP.xlsx"
Const strCompareDataStartCell As String = "A3"
Const strDataShtName As String =
"GM00410.BUY.HOLD.WASH"
Const strCompDataShtName As String = "slp"
Set wbkFile = OpenFile(strCompareFilePath, False, True)
If Not wbkFile Is Nothing Then
With wbkFile.Worksheets(strCompDataShtName)
Set rngData = .Range(strCompareDataStartCell)
If .Cells(.Rows.Count, rngData.Column).End(xlUp).Row >=
rngData.Row Then
Set rngData = .Range(rngData.Address, .Cells(.Rows.Count,
rngData.Column).End(xlUp).Address)
vararrDataToRemove = rngData.Value
wbkFile.Close 0
Set rngData = Nothing
Set wbkFile = OpenFile(strDataFilePath, False, False)
If Not wbkFile Is Nothing Then
With wbkFile.Worksheets(strDataShtName)
For lngLoop = LBound(vararrDataToRemove) To
UBound(vararrDataToRemove)
Set rngTemp = Nothing
If LenB(vararrDataToRemove(lngLoop, 1)) > 0 Then
On Error Resume Next
Set rngTemp = .UsedRange.Resize(,
1).Find(vararrDataToRemove(lngLoop, 1), LookIn:=xlValues, LookAt:=xlWhole)
On Error GoTo -1: On Error GoTo 0: Err.Clear
If Not rngTemp Is Nothing Then
If rngData Is Nothing Then
Set rngData = rngTemp
Else
Set rngData = Union(rngData,
rngTemp)
End If
End If
End If
Next lngLoop
If Not rngData Is Nothing Then
rngData.EntireRow.Delete
End If
If MsgBox("Do you want to save the file?",
vbQuestion + vbYesNo + vbDefaultButton2) = vbYes Then
wbkFile.Save
End If
End With
End If
End If
End With
End If
Set wbkFile = Nothing
Erase vararrDataToRemove
lngLoop = Empty
Set rngData = Nothing
Set rngTemp = Nothing
End Sub
Function OpenFile(ByVal strFilePath As String, ByVal blnUpdateLink As
Boolean, ByVal blnReadOnly As Boolean) As Object
Set OpenFile = Nothing
On Error Resume Next
Set OpenFile = Workbooks.Open(strFilePath, blnUpdateLink, blnReadOnly)
On Error GoTo -1: On Error GoTo 0: Err.Clear
End Function
On Thursday, 29 August 2013 04:15:27 UTC+5:30, sven...@gmail.com wrote:
>
> I have done a little work on my own and have a portion of it done. I can
> now automatically perform a vlookup against the parts listing by selecting
> the column the parts are located in and starting my macro. The code I used
> to do this is as follows:
>
> Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
> ActiveCell.Formula = "=VLOOKUP(RC[1],SLP.xlsx!C1,1,FALSE)"
> Range(ActiveCell, ActiveCell.Offset(60000, 0)).Select
> Selection.FillDown
> End Sub
>
> Now I need it to compare the values of the activecell and the
> activecell.offset(0,1) and if it is identical to delete the entire row.
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
