1
input:

45311       
0003ML00030-1   0003ML00030-2   0003ML00030-3
0003ML00030-1   0003ML00030-1

See my snap highlighted area need to select

See my snap highlighted area need to select, Last cell some times Blank and Also dynamic.

From the Above input, need to select All data and copy it. When i reuse the sheet The Data Range may vary, at the time standard formula not working properly.

Currently i use this VBA code.

Sub Macro()
 Sheets("input").Select
Range("D2").Select
   Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
   Selection.Copy
End Sub

When i use this code it select Extreme last cell (row 1048570 x col 41).

But in this case Last cell is (row4 x col7) like this way i need to select Dynamically if Data range vary

braX
  • 11,506
  • 5
  • 20
  • 33
baskar
  • 23
  • 7
  • Look [at this accepted answer here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vbahttps://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) – Ron Rosenfeld Apr 04 '20 at 11:28
  • But this marked answer- shows last Rows related details only. Any way thanks for your comment – baskar Apr 04 '20 at 13:26
  • You can apply similar logic to finding the last column – Ron Rosenfeld Apr 04 '20 at 14:27
  • Sorry i have no idea, today fully tried many times, not works. – baskar Apr 04 '20 at 15:00

1 Answers1

1

Here is the code:

Sub Macro()
Sheets("input").Activate
Range("b1").CurrentRegion.Copy

End Sub

Usually, to check the last cell's row No. you can use

Cells(Rows.Count, "B").End(xlUp).Row

to check the last cell's column No. you can use

Cells(2, Columns.Count).End(xlToLeft).Column

Here is the revised code and picture's demo.

Sub Macro()
Sheets("input").Activate
Set rng = Range("d1").CurrentRegion
rng.Offset(1, 3).Resize(rng.Rows.Count - 1, rng.Columns.Count - 3).Copy

End Sub

enter image description here

Anabas
  • 346
  • 1
  • 7
  • Its select from HOME cell A1, But my source data start from D2 – baskar Apr 04 '20 at 11:38
  • I will use `Range("D2: F" & r).Copy`, and let `r = Cells(Rows.Count, "D").End(xlUp).Row`, eg. assuming your data region end in col F. It is better to give a picture of your screen. – Anabas Apr 04 '20 at 11:48
  • But Col F is not fixed one. its dynamic – baskar Apr 04 '20 at 11:53
  • last column `Cells(2, Columns.Count).End(xlToLeft).Column` – Anabas Apr 04 '20 at 12:19
  • i am not understand. could you please update macro in answer field. – baskar Apr 04 '20 at 12:55
  • Please check the above revised macro code with the usage of `offset`. If it works please take it as answer.@baskar – Anabas Apr 04 '20 at 23:37
  • This code select full Active region, with offset . But i need to skip the unwanted column. Because as per your snap Column A:C no data, in my case Col A:C all filled with data. So this is not match with my requirement. – baskar Apr 05 '20 at 01:35
  • [See the current output as per your code](https://ibb.co/84r5sCQ) – baskar Apr 05 '20 at 01:52
  • 1
    you should change it to 'rng.Offset(1, 3).Resize(rng.Rows.Count - 1, rng.Columns.Count -3).Copy' .It's better to paste the screen capture at the beginning, It will show us your data structure. – Anabas Apr 05 '20 at 02:07
  • Wow its perfectly works, kindly update it to Answer Field. i accept this as my answer... – baskar Apr 05 '20 at 02:09