Solved: Row question

[vzachin]

hi,

i have data that begins in A5. so i would use the following and the macro will stop when it reaches a blank row in column A.

[VBA]For I = 5 To 9000
If Range("A" & I).FormulaR1C1 = "" Then
Range("A5").Select
MsgBox "COMPLETED"
Exit Sub
End If[/VBA]

the problem i have is that sometimes the data can begin anywhere in column A, beginning with row 5 and downward, 9000 being the max.

how can i modify my coding to run when it sees the first row populated and then stop with the next blank?

thanks

[austenr]

See if the KB entry helps you?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=417

[lenze]

Try something like this

Sub TestMe()
Dim firstRow As Integer
firstRow = Range("A5").End(xlDown).Row
For I = firstRow To 9000
If Range("A" & I).FormulaR1C1 = "" Then
    Cells(firstRow, 1).Select
    MsgBox "COMPLETED"
    Exit Sub
End If
Next I
End Sub

lenze

[mdmackillop]

or maybe
[VBA]
Sub TestMe()
Dim i As Long
For i = 5 To 9000
If Cells(i, 1) <> "" Then
'Do something
Cells(i, 3).Interior.ColorIndex = 6
Else
MsgBox "COMPLETED"
Exit Sub
End If
Next i
End Sub

[/VBA]

[Zack Barresse]

How about just ..

[vba]Range("A5").End(xldown).Offset(1).Select[/vba]

?? Maybe you don't need the Select, but that will give you the first blank after A5; unless A5 is the only value in that column, it will give you the last row in column A. It's faster than a loop for sure.

For ALL cells with a value in that column starting from row 5 and going down ...

[vba]Range("A5", Cells(Rows.Count, 1).end(xlup)).Select[/vba]

HTH

[acw]

Hi

Perhaps
[VBA]
Sub ccc()
If IsEmpty(Range("A5")) Then
firstrow = Range("A4").End(xlDown).Row
Else
firstrow = 5
End If
Range("a" & firstrow & ":A" & Cells(Rows.Count, 1).End(xlUp).Row).Select

End Sub
[/VBA]

Covers the situation where row 5 has data, but then again, does not. Selects the entire range in column A with data.


Tony

[mdmackillop]

Don't you love the questions that have such disparate solutions?

[lenze]

Don't you love the questions that have such disparate solutions?

Yes, but what happened to
vzachin

lenze

[johnske]

Don't you love the questions that have such disparate solutions?

And here's another
[vba]Option Explicit

Sub trythis()

Dim Cell As Range

For Each Cell In Range("A5:A9000")
If Not Cell.HasFormula Then
Range("A5").Select
MsgBox "COMPLETED"
Exit For
End If
Next

End Sub[/vba]Where I assume that, by using FormulaR1C1 in the OPs code, they're maybe looking for cells without data generated by w/s formulas...

[vzachin]

sorry for being missing in action. the post from lenze is what i am looking for. thanks for all the wonderful help