Use last row for both start and row and end column?

[priisha]

Hello,
I have an excel file where I need to loop through the data 12 times and what I want to do is copy the data from a certain range in this case column G2:H2
And then I want to paste it one row below and the last row with current data for column G:H and down to the last row based on column A.
The code below does not show the code for the A column but during the macro the A column will also have data in more row

So let say the first time prior to running the code below the column A has 25 rows
So I want the data to be pasted from G3:H25

Next time A column has data to row 50

Copy data from G25:H25
Paste to G26:H50
I don’t know how I can use last row count to the first range variable and the end cell

For i = 1 to 12

LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range(“G2:H2”).select
     Selection.copy
Activesheet.range(“G3:H" & LR+1).PasteSpecial xlPasteValues

Next

Please help me!

 

[offthelip]

Next time A column has data to row 50

What is writing into column A 26 to 50 in between the first iteration and the second? because this makes alot of difference as to how you solve this

 

[priisha]

What is writing into column A 26 to 50 in between the first iteration and the second? because this makes alot of difference as to how you solve this

Hi, another VBA code is copying data from another sheet to this column.
It’s pure text

 

[offthelip]

That means you can't do it 12 times in a loop because the "other" VBA code won't run in between the first iteration and the second iteration, probably the way to do this is to call your macro withthe code above from the macro that is doing the copying and forget about the loop . Just detect the last row in column A and copy the data you need, If you need to keep a record of where you are copying from you could do this with a Global variable to record the row.

 

[dreid1011]

Something similar to this perhaps, using two variables to find each column's last row...

Private Sub CopyData()
Dim lRowA As Long, lRowG As Long, i As Long

lRowA = Range("A" & Rows.Count).End(xlUp).Row
lRowG = Range("G" & Rows.Count).End(xlUp).Row

For i = lRowG + 1 To lRowA
    Range("G" & i & ":H" & i).Value = Range("G" & lRowG & ":H" & lRowG).Value
Next i

End Sub

 

[rlv01]

Perhaps.

Private Sub CopyData()
    Dim LRA As Long, LRG As Long
    Dim CopyRange As Range, PasteRange As Range
    
    With ActiveSheet
        Set CopyRange = .Range("G" & .Rows.Count).End(xlUp).Resize(1, 2)
        LRA = .Range("A" & .Rows.Count).End(xlUp).Row
        LRG = .Range("G" & .Rows.Count).End(xlUp).Row
        If LRA > LRG Then
            Set PasteRange = CopyRange.Offset(1).Resize(LRA - LRG)

            CopyRange.Copy
            PasteRange.PasteSpecial xlPasteValues
        Else
            'No copy takes place since the number of rows in with data in Column A is not greater than the
            'number of rows with data in Column G which is an unexplained scenario.
        End If
    End With
End Sub

 

[priisha]

Something similar to this perhaps, using two variables to find each column's last row...

Private Sub CopyData()
Dim lRowA As Long, lRowG As Long, i As Long

lRowA = Range("A" & Rows.Count).End(xlUp).Row
lRowG = Range("G" & Rows.Count).End(xlUp).Row

For i = lRowG + 1 To lRowA
    Range("G" & i & ":H" & i).Value = Range("G" & lRowG & ":H" & lRowG).Value
Next i

End Sub

many thanks the combination was exactly what i was looking for. Didn't use the code exactly as is but the combination enlightened me on how to move forward with my code. Once again thanks for your time and guidance

Range("G" & i & ":H" & i).Value = Range("G" & lRowG & ":H" & lRowG).Value

 

[dreid1011]

many thanks the combination was exactly what i was looking for. Didn't use the code exactly as is but the combination enlightened me on how to move forward with my code. Once again thanks for your time and guidance

You're welcome, and happy to help.