I keep getting a "Runtime error '9': Subscript out of range" on the
"Lastrow2" line. I have triple checked my path and everything seems fine.
Here is the code i am using:
Lastrow2 = Workbooks("C:\Documents and Settings\XXXXX\My Documents\Unit
Manager Files\BPI Part Numbers.xls").Sheets("Sheet1").Cells(Cells.Rows.Co unt,
"A").End(xlUp).Row
Set MyRange2 = Workbooks("C:\Documents and Settings\XXXXX\My Documents\Unit
Manager Files\BPI Part Numbers.xls").Sheets("Sheet1").Range("A1:A" & Lastrow2)
"Mike H" wrote:
Hi,
Like this
Lastrow2 = Workbooks("Book2.xls").Sheets("Sheet2").Cells(Cell s.Rows.Count,
"A").End(xlUp).Row
Set MyRange2 = Workbooks("Book2.xls").Sheets("Sheet2").Range("A1: A" &
Lastrow2)
This would now use a range from Book2.xls
Mike
"learningaccess" wrote:
If I wanted to link "Lastrow2" to another workbook as opposed to "Sheet2",
what would the code look like? I tried something on my own and it wouldn't
compile.
Thanks!
"Mike H" wrote:
Hi,
A bit thin on detail so some assumptions
The list of data to keep are in sheet 2 column A
Your part numbers are in column A of sheet 1
Right click sheet 1 tab, view code and paste this in and run it
Sub stance()
Dim MyRange1 As Range, MyRange2 As Range, CopyRange As Range
Dim There As Boolean
There = False
'Data to delete
Lastrow1 = Sheets("Sheet1").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange1 = Sheets("Sheet1").Range("A1:A" & Lastrow1)
'list of code numbers to delete
Lastrow2 = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange2 = Sheets("Sheet2").Range("A1:A" & Lastrow2)
For Each c In MyRange1
For Each d In MyRange2
If c.Value = d.Value Then
There = True
Exit For
End If
Next
If Not There Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
There = False
Next
If Not CopyRange Is Nothing Then
CopyRange.delete
End If
End Sub
Mike
"learningaccess" wrote:
The title may be confusing so I will try to explain better here. I have a
pile of data that I am using a macro to clean up to my liking. I would also
like to use VB to delete any rows that do not contain a specific part number
in column "A" for example:
SPN341014
SPN347016
SPN314017
There are 30 specific part numbers that I would like to limit my data to, so
I would like to for VB to search for those part numbers and delete all of the
other rows.