Using the UsedRange function within a Loop

[jufglanville]

I'm sure this is quite a simple question. I want to find a way to use the UsedRange function in conjunction with a loop.

Currently I am using the following code but I am having to manually input the value in Z to the bottom of the table. How do I replace this with the UsedRange function? I'm not able to go to the first cell that contains a blank as many cells within the range do!

Private Sub MergeDates2()


Dim i As Long

i = 1
Do While Cells(i, "Z").Value <> ""
If IsEmpty(Cells(i, "A")) = True Then
Cells(i, "A").Value = Cells(i, "C")

End If
i = i + 1
Loop

End Sub

 

[Fluff]

Are you trying to fill blank cells in col A with values in Col C until the last row of data?

 

[jufglanville]

That's right yeah

 

[Fluff]

How about

Sub FillColA()

    Dim UsdRws As Long
    Dim Cnt As Long
    
    UsdRws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For Cnt = 1 To UsdRws
        With Range("A" & Cnt)
            If .Value = "" Then .Value = .Offset(, 2).Value
        End With
    Next Cnt

End Sub

 

[jufglanville]

That's worked perfectly, thanks!!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Rick Rothstein]

That's worked perfectly, thanks!!

In case you might be interested, you can achieve the same results as Fluff's code without using any loops at all...

[table="width: 500"]
[tr]
	[td]Sub FillColA()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  Range("A2:A" & LastRow) = Evaluate(Replace("IF(A2:A#="""",IF(C2:C#="""","""",C2:C#),A2:A#)", "#", LastRow))
End Sub[/td]
[/tr]
[/table]