View Single Post
 
Old 08-31-2017, 05:57 AM
Seiquo Seiquo is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jun 2017
Posts: 4
Seiquo is on a distinguished road
Default

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.
Reply With Quote