VBA- Finding last populated cell in a column with blanks

[JoeRooney]

Hi ,

Wondering if anyone could advise me on a more efficient wayof coping & pasting data between two sheets.

Below is my code

Sheets("SCF_File").Select
Range("G2:G10000").Select
Selection.Copy
Sheets("Running File").Select
Range("E" & Rows.Count).End(xlUp).Offset(1,0).PasteSpecial


The above code works fine and pastes into the next blank cellbut I do not like to be restricted by specific ranges.

I was using the below code to select my data but some cells hadblanks so that would not get all the data.

Range("E2").Select
Range(Selection,Selection.End(xlDown)).Select
Selection.Copy

My question is , Column A willnot have blanks, is it possible to go the last cell in columnG based on the last cell populated in cell A and copy up to G2?

I.e. Cell A500 is the last populated cell in column A ,could I copy range G2:G500 ,

Hope that makes sense.

Thanks,

 

[JackDanIce]

Try:

Sub Test()

    Dim x  As Long
    
    With sheets("SCF_File")
        x = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
        sheets("Running File").Cells(Rows.Count, 5).End(xlUp).Offset(1).Resize(x).Value = .Cells(2, 7).Resize(x).Value
    End With
        
End Sub

Numbers in blue are column index values where A = 1, B = 2... Z = 26 etc

 

[JoeRooney]

Works perfect , thanks Jack

 

[JackDanIce]

You're welcome