Truthfully I could not get your code to work, but it did open my eyes to a
different way of working through my problem. I was originally hoping for a
macro that would function within an already open document and using
activecell parameters so that the macro could be used on files where the
partnumber column was in a different location. Now I simply created a
separate macro for each file which will automatically open each individual
file and be directly configured to each files inherent eccentricities. Then
by creating a master macro that initiates each individual macro in turn
making it so that adding new files to the list will not require re-working
the original macro.
On Wednesday, August 28, 2013 9:24:50 PM UTC-7, Lalit Mohan Pandey wrote:
>
> 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 =
> "C:\Users\UserName\Desktop\example file.xlsx"
> Const strCompareFilePath As String =
> "C:\Users\UserName\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.
