PDA

View Full Version : Solved: Row question



vzachin
06-01-2006, 09:45 AM
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.

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

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
06-01-2006, 11:08 AM
See if the KB entry helps you?

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

lenze
06-01-2006, 12:58 PM
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
06-01-2006, 01:35 PM
or maybe

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

Zack Barresse
06-01-2006, 03:01 PM
How about just ..

Range("A5").End(xldown).Offset(1).Select

?? 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 ...

Range("A5", Cells(Rows.Count, 1).end(xlup)).Select

HTH

acw
06-01-2006, 07:23 PM
Hi

Perhaps

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


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


Tony

mdmackillop
06-02-2006, 01:00 PM
Don't you love the questions that have such disparate solutions?

lenze
06-02-2006, 01:08 PM
Don't you love the questions that have such disparate solutions?
Yes, but what happened to
vzachin (http://vbaexpress.com/forum/member.php?u=4422) :dunno

lenze

johnske
06-02-2006, 07:09 PM
Don't you love the questions that have such disparate solutions?And here's another :devil2:
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 SubWhere I assume that, by using FormulaR1C1 in the OPs code, they're maybe looking for cells without data generated by w/s formulas...

vzachin
06-06-2006, 05:04 AM
sorry for being missing in action. the post from lenze is what i am looking for. thanks for all the wonderful help