Copy entire row if CountA <>0 to another sheet

[Harshil Mehta]

I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the entire row in sheet 1(A1) one below the another.

Sub copy_data()

Dim lr As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row

If WorksheetFunction.Sheets(3).CountA(RANGE("J:AM" & lr)) <> 0 Then
EntireRow.Copy
Sheets(1).RANGE("a1").PasteSpecial Paste:=xlPasteValues

End If

End Sub




Please help me!

 

[Harshil Mehta]

How about

Sub HarshilMehta()
   Dim Ary As Variant, Nary As Variant, Nary2 As Variant
   Dim r As Long, c As Long, nr As Long, cc As Long, nr2 As Long
   Dim Flg As Boolean
  
   With Sheets(3)
      Ary = .Range("A6:AM" & .Range("D" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   ReDim Nary2(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = 1 To UBound(Ary)
      Flg = True
      For c = 10 To UBound(Ary, 2)
         If Ary(r, c) <> "" Then
            Flg = False
            nr = nr + 1
            For cc = 1 To UBound(Ary, 2)
               Nary(nr, cc) = Ary(r, cc)
            Next cc
            Exit For
         End If
      Next c
      If Flg Then
         nr2 = nr2 + 1
         For cc = 1 To UBound(Ary, 2)
            Nary2(nr2, cc) = Ary(r, cc)
         Next cc
      End If
   Next r
   Sheets(1).Range("A1").Resize(nr, UBound(Ary, 2)).Value = Nary
   Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(nr2, UBound(Ary, 2)).Value = Nary2
End Sub

All the rows which satisfy the condition <> "" should be pasted on top and all the rows which satisfy the condition ="" should pasted in the end. See below for explanation. Everything ran perfectly expect this issue. Could you please help me fix this.

******Sheet1*****
1. rows that satisfy the condition <>""
2. rows that satisfy the condition <>""
3. rows that satisfy the condition =""

 

[Fluff]

That's what the code does.

 

[Harshil Mehta]

That's what the code does.

it does just the opposite what I wanted. Could you check it once again?

 

[Fluff]

If I start with this

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
5	Postcode	In Use?	Latitude	Longitude	Easting	Northing	GridRef	County	District	Ward	DistrictCode	WardCode	Country	CountyCode	Constituency	Introduced
6	AL4 0SP	Yes	51.75121	-0.28483	518495	207227	TL184072	Hertfordshire	St Albans	Colney Heath	E07000240	E05009028	England	E10000015	St Albans	########
7	AL4 8EG	Yes	51.79911	-0.31607	516216	212503	TL162125	Hertfordshire	St Albans
8	BS25 1QH	Yes	51.32351	-2.8008	344295	158571	ST442585	Somerset	Sedgemoor	Cheddar and Shipham	E07000188	E05008905	England	E10000027	Wells	########
9	CA10 1RE	Yes	54.68003	-2.596	361669	531826	NY616318	Cumbria	Eden
10	CH1 6LN	Yes	53.23844	-2.9201	338689	371662	SJ386716	Cheshire	Cheshire West and Chester	Saughall and Mollington	E06000050	E05008692	England	E11000004	City of Chester	########
11	CT16 1PF	Yes	51.12588	1.313926	631983	141511	TR319415	Kent	Dover
12	DE15 0QB	Yes	52.79074	-1.56753	429261	321551	SK292215	Derbyshire	South Derbyshire	Newhall and Stanton	E07000039	E05008817	England	E10000007	South Derbyshire	########
13	DE7 5QB	Yes	52.971	-1.30972	446454	341738	SK464417	Derbyshire	Erewash	Little Hallam	E07000036	E05010614	England	E10000007	Erewash	########
14	DL8 3AF	Yes	54.29027	-1.99533	400402	488293	SE004882	North Yorkshire	Richmondshire
15	EX33 1LR	Yes	51.10743	-4.16299	248674	136462	SS486364	Devon	North Devon	Braunton West	E07000043	E05003537	England	E10000008	North Devon	########
16	EX4 2LE	Yes	50.73842	-3.59347	287655	94412	SX876944	Devon	Teignbridge	Teignbridge North	E07000045	E05003610	England	E10000008	Central Devon	########
17	HX3 5NS	Yes	53.73978	-1.88809	407479	427050	SE074270	West Yorkshire	Calderdale	Ovenden	E08000033	E05001379	England	E11000019	Halifax	########
18	L9 3BU	Yes	53.45909	-2.96334	336134	396247	SJ361962	Merseyside	Liverpool	Warbreck	E08000012	E05000911	England	E11000013	Liverpool, Walton	########
19	LN2 1EW	Yes	53.23045	-0.53729	497737	371377	SK977713	Lincolnshire	Lincoln	Abbey	E07000138	E05010784	England	E10000019	Lincoln	########
20	LS7 4QY	Yes	53.8323	-1.53311	430826	437438	SE308374	West Yorkshire	Leeds	Chapel Allerton	E08000035	E05001419	England	E11000019	Leeds North East	########
21	ME12 4QF	Yes	51.40012	0.920474	603244	170835	TR032708	Kent	Swale	Sheppey East	E07000113	E05009562	England	E10000016	Sittingbourne and Sheppey	########
22	MK16 9HJ	Yes	52.09267	-0.652	492447	244671	SP924446	Buckinghamshire	Milton Keynes	Olney	E06000042	E05009418	England	E10000002	Milton Keynes North	########
Sheet2

I end up with

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	AL4 0SP	Yes	51.75121	-0.28483	518495	207227	TL184072	Hertfordshire	St Albans	Colney Heath	E07000240	E05009028	England	E10000015	St Albans	########
2	BS25 1QH	Yes	51.32351	-2.8008	344295	158571	ST442585	Somerset	Sedgemoor	Cheddar and Shipham	E07000188	E05008905	England	E10000027	Wells	########
3	CH1 6LN	Yes	53.23844	-2.9201	338689	371662	SJ386716	Cheshire	Cheshire West and Chester	Saughall and Mollington	E06000050	E05008692	England	E11000004	City of Chester	########
4	DE15 0QB	Yes	52.79074	-1.56753	429261	321551	SK292215	Derbyshire	South Derbyshire	Newhall and Stanton	E07000039	E05008817	England	E10000007	South Derbyshire	########
5	DE7 5QB	Yes	52.971	-1.30972	446454	341738	SK464417	Derbyshire	Erewash	Little Hallam	E07000036	E05010614	England	E10000007	Erewash	########
6	EX33 1LR	Yes	51.10743	-4.16299	248674	136462	SS486364	Devon	North Devon	Braunton West	E07000043	E05003537	England	E10000008	North Devon	########
7	EX4 2LE	Yes	50.73842	-3.59347	287655	94412	SX876944	Devon	Teignbridge	Teignbridge North	E07000045	E05003610	England	E10000008	Central Devon	########
8	HX3 5NS	Yes	53.73978	-1.88809	407479	427050	SE074270	West Yorkshire	Calderdale	Ovenden	E08000033	E05001379	England	E11000019	Halifax	########
9	L9 3BU	Yes	53.45909	-2.96334	336134	396247	SJ361962	Merseyside	Liverpool	Warbreck	E08000012	E05000911	England	E11000013	Liverpool, Walton	########
10	LN2 1EW	Yes	53.23045	-0.53729	497737	371377	SK977713	Lincolnshire	Lincoln	Abbey	E07000138	E05010784	England	E10000019	Lincoln	########
11	LS7 4QY	Yes	53.8323	-1.53311	430826	437438	SE308374	West Yorkshire	Leeds	Chapel Allerton	E08000035	E05001419	England	E11000019	Leeds North East	########
12	ME12 4QF	Yes	51.40012	0.920474	603244	170835	TR032708	Kent	Swale	Sheppey East	E07000113	E05009562	England	E10000016	Sittingbourne and Sheppey	########
13	MK16 9HJ	Yes	52.09267	-0.652	492447	244671	SP924446	Buckinghamshire	Milton Keynes	Olney	E06000042	E05009418	England	E10000002	Milton Keynes North	########
14	AL4 8EG	Yes	51.79911	-0.31607	516216	212503	TL162125	Hertfordshire	St Albans
15	CA10 1RE	Yes	54.68003	-2.596	361669	531826	NY616318	Cumbria	Eden
16	CT16 1PF	Yes	51.12588	1.313926	631983	141511	TR319415	Kent	Dover
17	DL8 3AF	Yes	54.29027	-1.99533	400402	488293	SE004882	North Yorkshire	Richmondshire
Sheet1

 

[Harshil Mehta]

If I start with this

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
5	Postcode	In Use?	Latitude	Longitude	Easting	Northing	GridRef	County	District	Ward	DistrictCode	WardCode	Country	CountyCode	Constituency	Introduced
6	AL4 0SP	Yes	51.75121	-0.28483	518495	207227	TL184072	Hertfordshire	St Albans	Colney Heath	E07000240	E05009028	England	E10000015	St Albans	########
7	AL4 8EG	Yes	51.79911	-0.31607	516216	212503	TL162125	Hertfordshire	St Albans
8	BS25 1QH	Yes	51.32351	-2.8008	344295	158571	ST442585	Somerset	Sedgemoor	Cheddar and Shipham	E07000188	E05008905	England	E10000027	Wells	########
9	CA10 1RE	Yes	54.68003	-2.596	361669	531826	NY616318	Cumbria	Eden
10	CH1 6LN	Yes	53.23844	-2.9201	338689	371662	SJ386716	Cheshire	Cheshire West and Chester	Saughall and Mollington	E06000050	E05008692	England	E11000004	City of Chester	########
11	CT16 1PF	Yes	51.12588	1.313926	631983	141511	TR319415	Kent	Dover
12	DE15 0QB	Yes	52.79074	-1.56753	429261	321551	SK292215	Derbyshire	South Derbyshire	Newhall and Stanton	E07000039	E05008817	England	E10000007	South Derbyshire	########
13	DE7 5QB	Yes	52.971	-1.30972	446454	341738	SK464417	Derbyshire	Erewash	Little Hallam	E07000036	E05010614	England	E10000007	Erewash	########
14	DL8 3AF	Yes	54.29027	-1.99533	400402	488293	SE004882	North Yorkshire	Richmondshire
15	EX33 1LR	Yes	51.10743	-4.16299	248674	136462	SS486364	Devon	North Devon	Braunton West	E07000043	E05003537	England	E10000008	North Devon	########
16	EX4 2LE	Yes	50.73842	-3.59347	287655	94412	SX876944	Devon	Teignbridge	Teignbridge North	E07000045	E05003610	England	E10000008	Central Devon	########
17	HX3 5NS	Yes	53.73978	-1.88809	407479	427050	SE074270	West Yorkshire	Calderdale	Ovenden	E08000033	E05001379	England	E11000019	Halifax	########
18	L9 3BU	Yes	53.45909	-2.96334	336134	396247	SJ361962	Merseyside	Liverpool	Warbreck	E08000012	E05000911	England	E11000013	Liverpool, Walton	########
19	LN2 1EW	Yes	53.23045	-0.53729	497737	371377	SK977713	Lincolnshire	Lincoln	Abbey	E07000138	E05010784	England	E10000019	Lincoln	########
20	LS7 4QY	Yes	53.8323	-1.53311	430826	437438	SE308374	West Yorkshire	Leeds	Chapel Allerton	E08000035	E05001419	England	E11000019	Leeds North East	########
21	ME12 4QF	Yes	51.40012	0.920474	603244	170835	TR032708	Kent	Swale	Sheppey East	E07000113	E05009562	England	E10000016	Sittingbourne and Sheppey	########
22	MK16 9HJ	Yes	52.09267	-0.652	492447	244671	SP924446	Buckinghamshire	Milton Keynes	Olney	E06000042	E05009418	England	E10000002	Milton Keynes North	########
Sheet2

I end up with

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	AL4 0SP	Yes	51.75121	-0.28483	518495	207227	TL184072	Hertfordshire	St Albans	Colney Heath	E07000240	E05009028	England	E10000015	St Albans	########
2	BS25 1QH	Yes	51.32351	-2.8008	344295	158571	ST442585	Somerset	Sedgemoor	Cheddar and Shipham	E07000188	E05008905	England	E10000027	Wells	########
3	CH1 6LN	Yes	53.23844	-2.9201	338689	371662	SJ386716	Cheshire	Cheshire West and Chester	Saughall and Mollington	E06000050	E05008692	England	E11000004	City of Chester	########
4	DE15 0QB	Yes	52.79074	-1.56753	429261	321551	SK292215	Derbyshire	South Derbyshire	Newhall and Stanton	E07000039	E05008817	England	E10000007	South Derbyshire	########
5	DE7 5QB	Yes	52.971	-1.30972	446454	341738	SK464417	Derbyshire	Erewash	Little Hallam	E07000036	E05010614	England	E10000007	Erewash	########
6	EX33 1LR	Yes	51.10743	-4.16299	248674	136462	SS486364	Devon	North Devon	Braunton West	E07000043	E05003537	England	E10000008	North Devon	########
7	EX4 2LE	Yes	50.73842	-3.59347	287655	94412	SX876944	Devon	Teignbridge	Teignbridge North	E07000045	E05003610	England	E10000008	Central Devon	########
8	HX3 5NS	Yes	53.73978	-1.88809	407479	427050	SE074270	West Yorkshire	Calderdale	Ovenden	E08000033	E05001379	England	E11000019	Halifax	########
9	L9 3BU	Yes	53.45909	-2.96334	336134	396247	SJ361962	Merseyside	Liverpool	Warbreck	E08000012	E05000911	England	E11000013	Liverpool, Walton	########
10	LN2 1EW	Yes	53.23045	-0.53729	497737	371377	SK977713	Lincolnshire	Lincoln	Abbey	E07000138	E05010784	England	E10000019	Lincoln	########
11	LS7 4QY	Yes	53.8323	-1.53311	430826	437438	SE308374	West Yorkshire	Leeds	Chapel Allerton	E08000035	E05001419	England	E11000019	Leeds North East	########
12	ME12 4QF	Yes	51.40012	0.920474	603244	170835	TR032708	Kent	Swale	Sheppey East	E07000113	E05009562	England	E10000016	Sittingbourne and Sheppey	########
13	MK16 9HJ	Yes	52.09267	-0.652	492447	244671	SP924446	Buckinghamshire	Milton Keynes	Olney	E06000042	E05009418	England	E10000002	Milton Keynes North	########
14	AL4 8EG	Yes	51.79911	-0.31607	516216	212503	TL162125	Hertfordshire	St Albans
15	CA10 1RE	Yes	54.68003	-2.596	361669	531826	NY616318	Cumbria	Eden
16	CT16 1PF	Yes	51.12588	1.313926	631983	141511	TR319415	Kent	Dover
17	DL8 3AF	Yes	54.29027	-1.99533	400402	488293	SE004882	North Yorkshire	Richmondshire
Sheet1

Before & After

 

[Fluff]

Have you changed the code at all?

 

[Harshil Mehta]

Have you changed the code at all?

Sub HarshilMehta()
   Dim Ary As Variant, Nary As Variant, Nary2 As Variant
   Dim r As Long, c As Long, nr As Long, cc As Long, nr2 As Long
   Dim Flg As Boolean
  
   With Sheets(3)
      Ary = .Range("A7:AM" & .Range("D" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   ReDim Nary2(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = 1 To UBound(Ary)
      Flg = True
      For c = 10 To UBound(Ary, 2)
         If Ary(r, c) <> "" Then
            Flg = False
            nr = nr + 1
            For cc = 1 To UBound(Ary, 2)
               Nary(nr, cc) = Ary(r, cc)
            Next cc
            Exit For
         End If
      Next c
      If Flg Then
         nr2 = nr2 + 1
         For cc = 1 To UBound(Ary, 2)
            Nary2(nr2, cc) = Ary(r, cc)
         Next cc
      End If
   Next r
   Sheets(1).Range("A2").Resize(nr, UBound(Ary, 2)).Value = Nary
   Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(nr2, UBound(Ary, 2)).Value = Nary2
End Sub

 

[Fluff]

Ok you have nothing in col A, which is causing the problem
use

   Sheets(1).Range("D" & Rows.Count).End(xlUp).Offset(1, -3).Resize(nr2, UBound(Ary, 2)).Value = Nary2

 

[Harshil Mehta]

Ok you have nothing in col A, which is causing the problem
use

   Sheets(1).Range("D" & Rows.Count).End(xlUp).Offset(1, -3).Resize(nr2, UBound(Ary, 2)).Value = Nary2

Thanks mate. I really appreciate your time and efforts. Thank you so much.

 

[Fluff]

Glad we could help & thanks for the feedback.