How to run through columns placed in the middle of a table-CodePudding

I previously asked a question regarding running through columns and rows of a table. The following code is what helped me solve the problem.

(

Sub CheckColumns()
Dim rng As Range
Dim lCol As Long, lRow As Long

    lCol = Range("C2").End(xlToRight).Column
    lRow = Range("C2").End(xlDown).Row

    For Each rng In Range("D4", Cells(lRow, lCol))
        If IsNumeric(rng) = False Then
             MsgBox ("A number has to be entered " & "row " & rng.Row)
        End If
    Next rng

End Sub

CodePudding user response：

Assuming you want to find the max last row across all 3 columns (C, D, E) then you can just use the MAX function.

Usually when finding the last used row you go from the bottom up. The code you shared is actually surfacing the first blank row which will not always be the last row.

Sub CheckColumns()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<--- Update sheet name accordingly
Dim Target As Range
Dim lr As Long


'Find the MAX last row between columns C, D, E
lr = Application.WorksheetFunction.Max( _
        ws.Range("C" & ws.Rows.Count).End(xlUp).Row, _
        ws.Range("D" & ws.Rows.Count).End(xlUp).Row, _
        ws.Range("E" & ws.Rows.Count).End(xlUp).Row)
        
        
For Each Target In Range("C2:E" & lr)
    If Not IsNumeric(Target) Then
    
        'Do what what cell is not numeric?
        
    End If
Next Target

End Sub