Excel VBA

[fouling91]

Hi every one;
I'm looking for a VBA code capable of copying data from columns "C2:D227612" and pasting them into empty rows in "A3:B227612". For example: range C2:D2 must be pasted into A3:B3 (empty row) and repeat this for all 227612 data. I need to put the Longitude-Latitude contained in columns C and D just below each Longitude-latitude combination in columns A. Can You Help Me please

 

[jkpieterse]

Assuming the exact same layout:

Sub FillTheGaps()
    With ActiveSheet
        With Intersect(.UsedRange, .Range("A:B")).SpecialCells(xlCellTypeBlanks)
            .Formula = "=c2"
            .Value = .Value
        End With
    End With
End Sub

 

[fouling91]

Assuming the exact same layout:

Sub FillTheGaps()
    With ActiveSheet
        With Intersect(.UsedRange, .Range("A:B")).SpecialCells(xlCellTypeBlanks)
            .Formula = "=c2"
            .Value = .Value
        End With
    End With
End Sub

Hi , thanks, but the code dosn't work. The code only copies the columns I want. Why did you put "=c2"?. I'd like to copy the lines contained in the "C2: D227612" range and paste them into the empty boxes in (A2: B227612), respecting the order. For example: Row "C2:D2" should be copied and pasted into "A3:B3", and so on for the rest up to "Cn:Dn" in "An+1:Bn+1". After executing your code it looked like this :

 

[jkpieterse]

As I said, my code assumes the EXACT same layout, so it assumes the first empty cell in column A is cell A3. IN cell A3, my code puts the formula =C2. ANd the line is constructed in such a way that this formula is then "copied" to all empty rows in columns A and B, thus filling them with the content two columns to the right and one row up.

To cater for different positions of the gaps, please adapt the code to:

Sub FillTheGaps()
    With ActiveSheet
        With Intersect(.UsedRange, .Range("A:B")).SpecialCells(xlCellTypeBlanks)
            .FormulaR1C1 = "=R[-1]C[2]"
            .Value = .Value
        End With
    End With
End Sub

 

[fouling91]

Thanks for your code, but it didn't work. Can you adjust it?

 

[fouling91]

Edit: Nvm, wrong version of excel.

Thanks, i will try this formula and return to u

 

[dreid1011]

As I said, my code assumes the EXACT same layout, so it assumes the first empty cell in column A is cell A3. IN cell A3, my code puts the formula =C2. ANd the line is constructed in such a way that this formula is then "copied" to all empty rows in columns A and B, thus filling them with the content two columns to the right and one row up.

To cater for different positions of the gaps, please adapt the code to:

Sub FillTheGaps()
    With ActiveSheet
        With Intersect(.UsedRange, .Range("A:B")).SpecialCells(xlCellTypeBlanks)
            .FormulaR1C1 = "=R[-1]C[2]"
            .Value = .Value
        End With
    End With
End Sub

Upon testing your code, the issue seems to be with this line:

            .Value = .Value

 

[fouling91]

Edit: Nvm, wrong version of excel.

Thanks, i will try this formula and return to u

 

[fouling91]

yeah wrong version of Excel, i have 2016 version

 

[dreid1011]

Upon testing your code, the issue seems to be with this line:

            .Value = .Value

Slight adjustment to the code:

Sub FillTheGaps()
    With ActiveSheet
        With Intersect(.UsedRange, .Range("A:B"))
            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C[2]"
            .Value = .Value
        End With
    End With
End Sub