Excelのこととか色々

マクロをどんな Excel ファイルにも実行できるように設定して、3秒かかる操作も1秒で完了。あといろいろ書いてみます・・・

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

   Excel/English

 

※ 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. 

                    

Macro

※ 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

www.tuna-kichi.com

 

 About setting macros on the ribbon

www.tuna-kichi.com

 

Link other Excel pages

 

 _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

 

 

 

 

PVアクセスランキング にほんブログ村 Excelのこととか色々 - にほんブログ村 にほんブログ村 子育てブログ 幼稚園児育児へ にほんブログ村 英語ブログ 初心者英語へ にほんブログ村 IT技術ブログ VBAへ