Do until last row

**Riosmitham** · 01-21-2011, 05:45 AM

I've been trying to work out how to format cells until the last cell that has data in and then stop. I have written the code for the formatting but just need some help with making it run until the last cell with a value in it. I would prefer it in this format.

Sub Vibration()
Dim i as Long
i=0
For i = 57 to (code for last cell with value in it here)

#FORMATTING CODE#

next i
End Sub

The column, if needed, is BA.
Thanks

**sweep** · 01-21-2011, 05:55 AM

Hi,

There's no need to loop, you can set a variable to the range containing data.

Sub a()
Dim r As Range
Set r = Range(Range("B57"), Range("B" & Rows.Count).End(xlUp))
r.Interior.ColorIndex = 3
End Sub

**Riosmitham** · 01-21-2011, 07:40 AM

I really needed it to loop as i have many if statments specific to the cell being formatted within the loop.
I've managed to do it myself though.

Dim i as Long
i = 57
With Sheets("Design Database")
    Do While i <= .Rows.Count
    If .Cells(i, 2) <> "" Then


#FORMATTING#

    Else
    Exit Do
    End If
    i = i + 1
    Loop
    End With