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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.