how to iterate over all rows of a excel sheet in VBA

Question

I have this code (This code is in Access VBA which tries to read an excel file and after checking, possibly import it):

Set ExcelApp = CreateObject("Excel.application")
Set Workbook = ExcelApp.Workbooks.Open(FileName)
Set Worksheet = Workbook.Worksheets(1)

now I want to iterate over all rows of the excel worksheet. I want something such as this:

for each row in Worksheet.rows
      ProcessARow(row)
next row

where

function ProcessARow(row as ???? )
   ' process a row
   ' how Should I define the function
   ' how can I access each cell in the row
   ' Is there any way that I can understand how many cell with data exist in the row

end function

My questions:

1. How to define the for each code that it iterate correctly on all rows that has data?
2. How to define ProcessARow properly
3. How to get the value of each cell in the row.
4. How to find how many cell with data exist in the row?
5. Is there any way that I detect what is the data type of each cell?

edit 1

The link solves on problem :

How to define the for each code that it iterate correctly on all rows that has data? 

but what about other questions?

For example, how to define ProcessARow correctly?

Answer 1

While I like the solution offered by @krazynhazy I believe that the following solution might be slightly shorter and closer to what you asked for. Still, I'd use the CellType function offered by Krazynhazy rather than all the Iif I currently have in the below code.

Option Explicit

Sub AllNonEmptyCells()

Dim rngRow As Range
Dim rngCell As Range
Dim wksItem As Worksheet

Set wksItem = ThisWorkbook.Worksheets(1)

On Error GoTo EmptySheet
For Each rngRow In wksItem.Cells.SpecialCells(xlCellTypeConstants).EntireRow.Rows
    Call ProcessARow(wksItem, rngRow.Row)
Next rngRow

Exit Sub

EmptySheet:
    MsgBox "Sheet is empty." & Chr(10) & "Aborting!"
    Exit Sub

End Sub

Sub ProcessARow(wksItem As Worksheet, lngRow As Long)

Dim rngCell As Range

Debug.Print "Cells to process in row " & lngRow & ": " & wksItem.Range(wksItem.Cells(lngRow, 1), wksItem.Cells(lngRow, wksItem.Columns.Count)).SpecialCells(xlCellTypeConstants).Count
For Each rngCell In wksItem.Range(wksItem.Cells(lngRow, 1), wksItem.Cells(lngRow, wksItem.Columns.Count)).SpecialCells(xlCellTypeConstants)
    Debug.Print "Row: " & lngRow, _
                "Column: " & rngCell.Column, _
                "Value: " & rngCell.Value2, _
                IIf(Left(rngCell.Formula, 1) = "=", "Formula", IIf(IsDate(rngCell.Value), "Date", IIf(IsNumeric(rngCell.Value2), "Number", "Text")))
Next rngCell

End Sub

Note, that you have to call the sub to call a row must also include the sheet on which a row should be processed.

Answer 2

Unfortunately you questions are very broad however I believe the below sub routine can show you a few ways of achieving what you are after. In regards to what datatype each cell is more involved as it depends what data type you wish to compare it to however I have included some stuff to hopefully help.

sub hopefullyuseful()
    dim ws as worksheet
    dim rng as Range
    dim strlc as string
    dim rc as long, i as long
    dim lc as long, j as long
    dim celltoprocess as range
    set ws = activeworkbook.sheets(activesheet.name)
    strlc = ws.cells.specialcells(xlcelltypeLastCell).address
    set rng = ws.range("A1:" & lc)

    rc = rng.rows.count()
    debug.print "Number of rows: " & rc

    lc = rng.columns.count()
    debug.print "Number of columns: " & lc
    '
    'method 1 looping through the cells'
    for i = 1 to rc
        for j = 1 to lc
            set celltoprocess = ws.cells(i,j)
            'this gives you a cell object at the coordinates of (i,j)'
            '[PROCESS HERE]'
            debug.print celltoprocess.address & "  is  celltype: " & CellType(celltoprocess)
            'here you can do any processing you would like on the individual cell if needed however this is not the best method'
            set celltoprocess = nothing
        next j
    next i
    'method 2 looping through the cells using a for each loop'
    for each celltoprocess in rng.cells
        debug.print celltoprocess.address & "  is  " & CellType(celltoprocess)
    next celltoprocess

    'if you just need the data in the cells and not the actual cell objects'
    arrOfCellData = rng.value

    'to access the data'
    for i = lbound(arrOfCellData,1) to ubound(arrOfCellData,1)
        'i = row'
        for j = lbound(arrOfCellData,2) to ubound(arrOfCellData,2)
            'j = columns'
            debug.print "TYPE: "  & typename(arrOfCellData(i,j))  & "   character count:" & len(arrOfCellData(i,j))

        next j
    next i
    set rng=nothing
    set celltoprocess = nothing
    set ws = nothing
end sub

Function CellType(byref Rng as range) as string
    Select Case True
        Case IsEmpty(Rng)
            CellType = "Blank"
        Case WorksheetFunction.IsText(Rng)
            CellType = "Text"
        Case WorksheetFunction.IsLogical(Rng)
            CellType = "Logical"
        Case WorksheetFunction.IsErr(Rng)
            CellType = "Error"
        Case IsDate(Rng)
            CellType = "Date"
        Case InStr(1, Rng.Text, ":") <> 0
            CellType = "Time"
        Case IsNumeric(Rng)
            CellType = "Value"
    End Select
end function

sub processRow(byref rngRow as range)
    dim c as range
    'it is unclear what you want to do with the row however... if you want
    'to do something to cells in the row this is how you access them
    'individually

   for each c in rngRow.cells
        debug.print "Cell " & c.address & " is in Column " & c.column & " and Row " & c.row & " has the value of " & c.value
   next c 
   set c = nothing
   set rngRow = nothing

exit sub

if you want your other questions answered you will have to be more specific as to what you are trying to accomplish

Answer 3

If you need the values in the Row, you need use the 'Value' Property and after do an cycle to get each value

for each row in Worksheet.rows
      Values=row.Value
      For each cell in Values
          ValueCell=cell
      next cell
next row