VBA Copy Data from one sheet to another - Some cells in rows are blank and current method stops at blank cell.

[Trebor200]

Hi All,

I currently use basic VBA code to select cell go down and left / right and select all data for copy.. this fails where i may have blank cells, in particular the cell i select and use selection.end down

As my data can have missing values in some cells is there a better way to select the data?

Current code below and sample data, as you can see there is some blank cells in each of the columns but i want to copy all data.

I want to copy all data from row 3..

Sub CopyData ()
    Range("G3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Copy
End Sub

Book1
	A	B	C	D	E	F	G
1	Record	ID	Account	Created On	Type	Descr	Descr 2
2
3	1.67E+08	29903055	80285559	12.04.2021	abc1	xyz1	sda1
4		23343936	80287280	14.04.2021	abc2	xyz2	sda2
5	1.67E+08	29124792	80301566	06.05.2021	abc3	xyz3	sda3
6	1.64E+08	23499466	80334249	30.06.2021	abc4	xyz4	sda4
7	1.67E+08		80336921	06.07.2021		xyz5	sda5
8	1.67E+08	30112024	80338938	08.07.2021	abc6	xyz6	sda6
9	1.68E+08	30686610	80339260	09.07.2021	abc7	xyz7	sda7
10		30110695	80339378		abc8		sda8
11	1.64E+08	22974751	80342827	15.07.2021	abc9	xyz9	sda9
12	1.68E+08	30611500	80344158	17.07.2021	abc10	xyz10
13	1.68E+08	30800115	80346117	21.07.2021	abc11	xyz11	sda11
14	1.66E+08	28221345	80355126	04.08.2021	abc12	xyz12	sda12
15	1.68E+08	30981311	80355521	05.08.2021		xyz13	sda13
16	1.67E+08	30389503	80356221	06.08.2021	abc14	xyz14	sda14
17	1.67E+08	28765685		11.08.2021	abc15	xyz15	sda15
18	1.68E+08	30673918	80359472	12.08.2021	abc16	xyz16	sda16
19	1.65E+08	25741372	80365654	23.08.2021	abc17	xyz17
20	1.68E+08	30756877	80373115	03.09.2021	abc18	xyz18	sda18
21		31084666	80374056	06.09.2021	abc19	xyz19	sda19
22	1.67E+08	30389520	80383455	21.09.2021	abc20	xyz20	sda20
23	1.68E+08	30673918	80390880	01.10.2021	abc21	xyz21	sda21
24	1.68E+08	31006957	80391482	01.10.2021	abc22	xyz22	sda22
25	1.68E+08	31121503	80391739	02.10.2021	abc23	xyz23	sda23
26	1.67E+08	29856848	80394893	07.10.2021	abc24	xyz24	sda24
Sheet1

 

[Fluff]

How about

Sub Trebor()
   Dim UsdRws As Long
   
   UsdRws = Range("A:G").Find("*", , , , xlByRows, xlPrevious, , , False).Row
   Range("A3:G" & UsdRws).Copy
End Sub

 

[Trebor200]

How about

Sub Trebor()
   Dim UsdRws As Long
  
   UsdRws = Range("A:G").Find("*", , , , xlByRows, xlPrevious, , , False).Row
   Range("A3:G" & UsdRws).Copy
End Sub

Perfect, Thank you...

 

[Fluff]

You're welcome & thanks for the feedback.