Good morning all-
I'm sure the answer is staring at me in the face, but I've wasted an entire day trying every syntax under the sun. I've also read through many similar posts, but none of the solutions seem to work for my situation.
Background: I'm running a macro on a rather lengthy spreadsheet. First step is that it creates a hidden sheet ("Current PMs") containing 2 columns (A: last names, B: first names). My code also assigns each list a to named range (LastNames & FirstNames). The list is subject to change as head count changes so the code treats the list as a dynamic range by finding the last row. Code is posted below...this part works perfectly. I've even confirmed the named ranges contain the proper data sets. I also believe I've made them global for the entire workbook to access.
'==========================================
'Create a named list for LastNames
Set rMyRg = Range([A1], [A1].End(xlDown))
ActiveWorkbook.Names.Add Name:="LastNames", RefersToR1C1:="='" & _
ActiveSheet.Name & "'!" & rMyRg.Address(ReferenceStyle:=xlR1C1)
'Create a named list for FirstNames
Set rMyRg = Range([B1], [B1].End(xlDown))
ActiveWorkbook.Names.Add Name:="FirstNames", RefersToR1C1:="='" & _
ActiveSheet.Name & "'!" & rMyRg.Address(ReferenceStyle:=xlR1C1)
'Hide sheet
Sheets("Current PMs").Visible = xlSheetHidden
'==========================================
Problem: Further down in the macro, I want to compare a column of the master spreadsheet against the LastNames range previously defined. While I think I have the syntax correct for referring to the named range, I thought I was in the clear. I want to delete all rows that DO NOT contain a value found in my named range (L. Here's what I've got thanks to help in another post.
'==========================================
Sub EdisonTrim()
Dim PM_Range As Range
Dim PMCol As Range
Dim bRow As Long
bRow = Cells(Rows.Count, Columns("F").Column).End(xlUp).Row
'Set PM_Range to the LastNames range defined on the Current PMs tab
Set PM_Range = Range("LastNames")
'Set PMCol to the truncated PM list in column F on the master sheet
Set PMCol = Range("F2:F" & bRow)
For Each cell In PMCol
If Application.WoksheetFunction.CountIf(PM_Range, cell.Value) = 0 Then
cell.EntireRow.Delete
End If
Next cell
End Sub
'==========================================
Every time I run this, I get an error "Object doesn't support this property or method."
Does anything stand out here? I thought my syntax was correct, but something is obviously amiss.
As always, your collective wisdom is much appreciated.
-Brian Bordieri
I'm sure the answer is staring at me in the face, but I've wasted an entire day trying every syntax under the sun. I've also read through many similar posts, but none of the solutions seem to work for my situation.
Background: I'm running a macro on a rather lengthy spreadsheet. First step is that it creates a hidden sheet ("Current PMs") containing 2 columns (A: last names, B: first names). My code also assigns each list a to named range (LastNames & FirstNames). The list is subject to change as head count changes so the code treats the list as a dynamic range by finding the last row. Code is posted below...this part works perfectly. I've even confirmed the named ranges contain the proper data sets. I also believe I've made them global for the entire workbook to access.
'==========================================
'Create a named list for LastNames
Set rMyRg = Range([A1], [A1].End(xlDown))
ActiveWorkbook.Names.Add Name:="LastNames", RefersToR1C1:="='" & _
ActiveSheet.Name & "'!" & rMyRg.Address(ReferenceStyle:=xlR1C1)
'Create a named list for FirstNames
Set rMyRg = Range([B1], [B1].End(xlDown))
ActiveWorkbook.Names.Add Name:="FirstNames", RefersToR1C1:="='" & _
ActiveSheet.Name & "'!" & rMyRg.Address(ReferenceStyle:=xlR1C1)
'Hide sheet
Sheets("Current PMs").Visible = xlSheetHidden
'==========================================
Problem: Further down in the macro, I want to compare a column of the master spreadsheet against the LastNames range previously defined. While I think I have the syntax correct for referring to the named range, I thought I was in the clear. I want to delete all rows that DO NOT contain a value found in my named range (L. Here's what I've got thanks to help in another post.
'==========================================
Sub EdisonTrim()
Dim PM_Range As Range
Dim PMCol As Range
Dim bRow As Long
bRow = Cells(Rows.Count, Columns("F").Column).End(xlUp).Row
'Set PM_Range to the LastNames range defined on the Current PMs tab
Set PM_Range = Range("LastNames")
'Set PMCol to the truncated PM list in column F on the master sheet
Set PMCol = Range("F2:F" & bRow)
For Each cell In PMCol
If Application.WoksheetFunction.CountIf(PM_Range, cell.Value) = 0 Then
cell.EntireRow.Delete
End If
Next cell
End Sub
'==========================================
Every time I run this, I get an error "Object doesn't support this property or method."
Does anything stand out here? I thought my syntax was correct, but something is obviously amiss.
As always, your collective wisdom is much appreciated.
-Brian Bordieri
