问如何在VBA中迭代excel工作表的所有行

EN

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

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

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