How to loop copy / paste until cell in range IsEmpty

[brentix]

I'm only just now starting to do Loops (i've so far managed to avoid them). However i now want to create a loop that will loop through a range until it finds a cell that is empty.

For each cell that is not empty I want it to copy and paste to maintain the formulas ine sheet as part of an edit / delete system.

Range would be Range("A5:A200")

the following code is copy and paste for 1 row where the cell shows a number (i'd prefer it to check for any value - but i'll cover that when i have a working loop)

range("A5").Select 'starting point
    If IsNumeric(Selection.Value) Then
    Selection.Offset(-1, 1).Copy
    Selection.Offset(0, 1).PasteSpecial
    Selection.Offset(-1, 1).Copy
    Selection.Offset(0, 1).PasteSpecial
    Selection.Offset(-1, 1).Copy
    Selection.Offset(0, 1).PasteSpecial
    Selection.Offset(-1, 1).Copy
    Selection.Offset(0, 1).PasteSpecial
    Selection.Offset(-1, 1).Copy
    Selection.Offset(0, 1).PasteSpecial
    End If

Essentially i want it to loop through the range until the first empty cell but I just can't get my head around it, any help or pointers would be much appreciated as I just can't figure out where to start.

 

[JackDanIce]

Is this what you want?

Dim i as Long
i = 5
Do While Not IsEmpty(Range("A" & i))
     If IsNumeric(Range("A" & i)) Then
        Range("B" & i-1, "F" & i-1).Copy
        Range("C" & i).PasteSpecial Paste:=xlValues
     End If
     i = i + 1
Loop

 

[brentix]

Fantastic, it was almost there but you helped me figure out exactly what i needed so big thanks, i've attached the code that works for me

Dim i As Long
i = 2
Do While Not IsEmpty(range("A" & i))
     If IsNumeric(range("A" & i)) Then
        range("B" & i - 1, "B" & i).Copy
        range("B" & i).PasteSpecial Paste:=xlValues
        range("C" & i - 1, "C" & i).Copy
        range("C" & i).PasteSpecial Paste:=xlValues
        range("D" & i - 1, "D" & i).Copy
        range("D" & i).PasteSpecial Paste:=xlValues
        range("E" & i - 1, "E" & i).Copy
        range("E" & i).PasteSpecial Paste:=xlValues
        range("F" & i - 1, "F" & i).Copy
        range("F" & i).PasteSpecial Paste:=xlValues
     End If
     i = i + 1
Loop

I knew I was nissing something, I hadn't even considered the

Do While Not IsEmpty(range("A" & i))

part
This a huge help so big thanks again as this is also a big insight into other Loops i'll make in future.

 

[JackDanIce]

You could use this instead:

Application.ScreenUpdating = False
Dim i As Long
i = 2
Do While Not IsEmpty(Range("A" & i))
     If IsNumeric(Range("A" & i)) Then
        Range("B" & i - 1, "F" & i).Copy
        Range("B" & i).PasteSpecial Paste:=xlValues
     End If
     i = i + 1
Loop
Application.ScreenUpdating = True

 

[brentix]

good point i missed that one
your version duplicates the line past the value row if you press the button again so amended
range("B" & i - 1, "F" & i).Copy
with
range("B" & i - 1, "F" & i - 1).Copy
now it doesn't go past the last value row

Dim i As Long
i = 2
Do While Not IsEmpty(range("A" & i))
     If Not IsEmpty(range("A" & i)) Then
        range("B" & i - 1, "F" & i - 1).Copy
        range("B" & i).PasteSpecial Paste:=xlValues
     End If
     i = i + 1
Loop

it's perfect now cheers

 

[brentix]

i see you had that in your original version, so sorry i missed it.

 

[JackDanIce]

No worries, glad it works

 

[brentix]

just one other observation
if you use
range("B" & i).PasteSpecial
rather than
range("B" & i).PasteSpecial Paste:=xlValues

you can use it for updating and maintaining formulas

I love it when a plan comes together