Iterating through populated rows

Question

So I am trying to iterate through a worksheet in an Excel spreadsheet using VBA  I want to iterate through each row  and then through each column  and despite googling  I can t actually find an intuitive way to do this   I m assuming that the first cell of a row must be populated  if its not  then that must be the end  I can enforce this   My current approach is to iterate through the rows  then try and get the value of the first cell  but I can t figure it out  I ve come across some questions here and elsewhere that use ranges and such  but nothing that helps me write code   The current approach is   Set sh   ActiveSheet RowCount   0 For Each rw In sh Rows      If Row Cells 1  1  Value      Then Exit For     RowCount   RowCount   1 Next rw MsgBox  RowCount    Now when I run this  I get some huge number  which is wrong as the table only has about 25 rows  I commented the first line out as it wasn t working   What can I change in the first line in the For Loop to correctly break when it finds a row where the first cell is empty

User · Accepted Answer

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

User · Answer

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    Find the last row in column A that has data Loop through each row Find the last column in current row with data 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   amp  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

User · Answer

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

[excel] Iterating through populated rows

Examples related to excel

Examples related to vba