Excel のこととか楽天とか いろいろ書いてみます・・・

【Excel/VBA 04】Select and Copy to The End Row LastRow/Selection)



※ Please let me know if you find any English mistakes.


Pressing Ctrl + Shift + Down Arrow key select cells from the first to the end.


However, you cannot select the last row if there is a blank cell.

If there is only one blank cell, just press an arrow key once again,

but if there are hundreds of blank cells, you have to press the arrow key many times.


 Cannot reach the end row of the data


This macro resolves this problem. 



※ Select the first cell of the column before running this macro.

Sub CopyColumn()

 Dim r As Long 'Variable for a row number
 Dim c As Long 'Variable for a column number
 Dim LastR As Long 'Variable for the last row

 r = Selection.Row 'Set a row number of the selected cell
 c = Selection.Column 'Set a column number of the selected cell
 LastR = Cells(Rows.Count, c).End(xlUp).Row 'Find and set the last row number of the selected cell

 Range(Cells(r, c), Cells(LastR, c)).Copy 'This sentence copies selected cells
 'Change "Copy" to "Select" if you want just select
 'Range(Cells(r, c), Cells(LastR, c)).Select

End Sub


This macro selects one column even if there are merged cells. 

 Be selected one column


This macro copied cells for you then you can paste it anywhere you want. 


Write this macro on a module on the "Personal macro workbook",

and set this on the ribbon.

You can use it anytime in any workbook opened on your computer.


Set the macro to the Ribbon



 About personal macro workbook



 About setting macros on the ribbon



Link other Excel pages