Copy previous cells with two columns at the same time?

enter image description here

it is possible to copy cell values, 2 columns at the same time (values ​​differ in both columns = rows). Now I am doing it separately.

1:

 Do Until ActiveCell.Row >= LastRow
        If Trim(ActiveCell.Offset(1, 0)) = "" Then
        ActiveCell.Offset(1, 0).Value = ActiveCell
        End If
        ActiveCell.Offset(1, 0).Select
    Loop

and then again for the third column

    Cells(FirstRow + 2, 2).Select
  Do Until ActiveCell.Row >= LastRow
            If Trim(ActiveCell.Offset(1, 0)) = "" Then
            ActiveCell.Offset(1, 0).Value = ActiveCell
            End If
            ActiveCell.Offset(1, 0).Select
        Loop

Actually, I encoded it below, it goes at the same time, but the importer is slow

Sub Kopi()
Dim i, y As Integer
For i = 1 To 100
    For y = 1 To 100
If Trim(Cells(i + 1, 1)) = "" And Trim(Cells(y + 1, 2)) = "" Then
    Cells(i + 1, 1).Value = Cells(i, 1)
    Cells(y + 1, 2).Value = Cells(y, 2)
    End If

Next y
Next i
End Sub
+4
source share
1 answer

This is what you need? It goes through your empty cells in the given range ( A1:B10) and sets any spaces equal to the value above

Sub Test()
    Dim rng As Range, r As Range
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10").SpecialCells(xlCellTypeBlanks)

    For Each r In rng
        If Not r.Row = 1 Then r.Value = r.Offset(-1, 0).Value
    Next r
End Sub
+1
source

Source: https://habr.com/ru/post/1673360/


All Articles