Do Loop until end of a full column

[Soul777Toast]

Hi guys,

I want to execute a do loop on an excel column until I get to either an empty space or the bottom of the worksheet (I'm analyzing very large amounts of data, frequently I have full columns). Is there anyway to do something like

[vba]
' pseudocode
do until cell is eof
if next cell is empty then
exit do
end if
code
select next cell
loop
[/vba]
or do I have to just run

[vba]
'pseudocode
do until counter = 65536
if next cell is empty then
exit do
end if
code
select next cell
loop
[/vba]
I know the second option would work, but the perfectionist in me hates using predefined values and would love to be able to test for the end of the file ;p

Thanks guys!

[stanleydgrom]

Soul777Toast,

This works.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


[VBA]

Option Explicit
Sub Test()
Dim lngLastRow As Long
Dim lngLoopCtr As Long
lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
For lngLoopCtr = 1 To lngLastRow Step 1
If Cells(lngLoopCtr, "A") = "" Then
Cells(lngLoopCtr, "A").Select
Exit For
End If

'Code

Next lngLoopCtr
End Sub

[/VBA]


Have a great day,
Stan

[tstav]

This works

Have you considered the case where the last cell (rows.count,1) is not empty? the .end(xlup) property would shove you all the way up to probably the first cell in the column!

[tstav]

Hi soul777toast,
On each step, you first check that you have not exceeded the Rows.Count limit.
Then you check whether the current cell is empty or not.
[VBA]Sub DoWhile()
Dim i As Long
i = 1
With ActiveSheet
Do While i <= .Rows.Count
If .Cells(i, 1) <> "" Then
'Your code here
Else
Exit Do
End If
i = i + 1
Loop
End With
End Sub
[/VBA]

[stanleydgrom]

tstav,

Nice catch. Thanks.


Have a great day,
Stan

[mikerickson]

Rather than checking for empty cells, you could loop through only the filled cells.

For each oneCell in ActiveSheet.Range("A:A").SpecialCells(xlCellTypeConstants)
    Rem do stuff
Next oneCell

If none of the cells hold formulas, that would be one way to go.

Also, this will find the last filled cell in column A, without looping, even if Cells(Rows.Count,1) is filled
[VBA]Dim BottomCell as Range
With ThisWorkbook.Sheets("Sheet1").Range("A:A")

Set BottomCell = .Find(What:="*", After:=.Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
False)

End With[/VBA]

[stanleydgrom]

mikerickson,

Thank you.


Have a great day,
Stan

[tstav]

Rather than checking for empty cells, you could loop through only the filled cells[/vba]

The original post though was quite clear:

I want to execute a do loop on an excel column until I get to either an empty space or the bottom of the worksheet

Still, Mike the options you're referring to may certainly apply to other cases and the code is very very good!