Different sheet pasting

Question

I have written a code which gives me the errors (if any cell is non numeric) in a separate sheet called "Error_sheet".

But the output is a bit clumsy as it gives me non numeric cell address in a confusing fashion. Like the errors will not be pasted one after another. There will be some blanks in between if there are more than one non Numeric cells.

Sub Test()
    Dim LastRow As Long, i As Long
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To LastRow
        If IsNumeric(Range("A" & i).Value) Then

        Else
            Sheets("Error").Range("A" & Row).Value = "Error in" & i & " row of ColumnNAme"
            Row = Row + 1
        End If
    Next i
End Sub

It gives me output like shown below but can I get the output like Error in 7,14 rows of column name in a desired cell of "Error_sheet".

[![Output][1]][1] [1]: https://i.sstatic.net/JqXwq.png

Answer 1

My understanding of what you've written is that you want something like this.

Option Explicit

Sub Test()

' Unqualified book/sheet below, means code will always run the isnumeric check on the cells of the active sheet. Is that what you want? '

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row 

Dim Index as long
Dim i As Long
Dim NonNumericRows() as string
Redim NonNumericRows(1 to lastrow)

For i = 2 To LastRow

If not(IsNumeric(Range("A" & i).Value)) Then
Index = index + 1
NonNumericRows(Index) = cstr(i)
End if

Next i

Redim preserve NonNumericRows(1 to index)

Sheets("Error").Range("A1").Value = "Error in row(s): " & strings.join(nonnumericrows,", ") & " of ColumnNAme"

End Sub

Hope it works or helps.