Good morning Serge1p,
First thank you very much for sparing some of your time to help me. Much appreciated.
I copied your code in the Workbook1 "ThisWorkBook" code sheet as follows:
Code:
Private Function listNumbers(fileName As String, sheetName As String, columnName As String) As Long()
Dim result() As Long
Dim resultLength As Integer
Dim rangeName As String
Dim wb As Workbook
Dim rangeStartRow As Integer
Dim sh As Worksheet
Dim needToClose As Boolean
Dim i As Integer
needToClose = True
For i = 1 To Workbooks.Count
If Workbooks(i).FullName = fileName Then
needToClose = False
Set wb = Workbooks(i)
End If
Next
rangeStartRow = 2 ' change this as required
If needToClose Then
Set wb = Workbooks.Open(fileName, ReadOnly:=True)
End If
Set sh = wb.Worksheets(sheetName)
resultLength = sh.Cells(sh.Rows.Count, columnName).End(xlUp).Row - rangeStartRow + 1
ReDim result(resultLength - 1)
For i = 1 To resultLength
result(i - 1) = sh.Range(columnName & (rangeStartRow + i - 1)).Value
Next
listNumbers = result
If needToClose Then
wb.Close SaveChanges:=False
End If
End Function
Public Sub DeleteRows()
Dim numbers() As Long
Dim pos As Variant
Dim cellNumber As Long
Dim cl As Range
numbers = listNumbers("C:\Users\chevalls\Desktop\MyFile.xlsx", "Sheet1", "A")
For Each cl In ActiveSheet.Range("A:A").Cells
If Not IsNumeric(cl.Value) Then
Exit For
End If
cellNumber = cl.Value
pos = Application.Match(cellNumber, numbers, False)
If Not IsError(pos) Then
cl.Interior.ColorIndex = 1
End If
Next
End Sub
I changed :
- the RangeStartRow number;
- the file path, assuming that the path refers to my Workbook1; and
- added the "whatif" at the end.
When I try to run the macro, I get the error message "400"...
Do you have any idea of what I did wrong?
Thank you again.