Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Iterating through populated rows

Tags:

excel

vba

People also ask

How do I traverse data in Excel?

Here's how you can transpose cell content: Copy the cell range. Select the empty cells where you want to paste the transposed data. On the Home tab, click the Paste icon, and select Paste Transpose.


For the benefit of anyone searching for similar, see worksheet .UsedRange,
e.g. ? ActiveSheet.UsedRange.Rows.Count
and loops such as
For Each loopRow in Sheets(1).UsedRange.Rows: Print loopRow.Row: Next


It looks like you just hard-coded the row and column; otherwise, a couple of small tweaks, and I think you're there:

Dim sh As Worksheet
Dim rw As Range
Dim RowCount As Integer

RowCount = 0

Set sh = ActiveSheet
For Each rw In sh.Rows

  If sh.Cells(rw.Row, 1).Value = "" Then
    Exit For
  End If

  RowCount = RowCount + 1

Next rw

MsgBox (RowCount)

I'm going to make a couple of assumptions in my answer. I'm assuming your data starts in A1 and there are no empty cells in the first column of each row that has data.

This code will:

  1. Find the last row in column A that has data
  2. Loop through each row
  3. Find the last column in current row with data
  4. Loop through each cell in current row up to last column found.

This is not a fast method but will iterate through each one individually as you suggested is your intention.


Sub iterateThroughAll()
    ScreenUpdating = False
    Dim wks As Worksheet
    Set wks = ActiveSheet

    Dim rowRange As Range
    Dim colRange As Range

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

    Set rowRange = wks.Range("A1:A" & LastRow)

    'Loop through each row
    For Each rrow In rowRange
        'Find Last column in current row
        LastCol = wks.Cells(rrow, wks.Columns.Count).End(xlToLeft).Column
        Set colRange = wks.Range(wks.Cells(rrow, 1), wks.Cells(rrow, LastCol))

        'Loop through all cells in row up to last col
        For Each cell In colRange
            'Do something to each cell
            Debug.Print (cell.Value)
        Next cell
    Next rrow
    ScreenUpdating = True
End Sub