VBA Count Non Blank Rows On Sheet 1 and Copy Paste Based on Counted Rows in Sheet 2

[bquek]

The following VBA code to count rows but it only counted one row out of 10 rows. The count starts at row 4 and cells in column A contain formula. How to modify the code to be able to count the non blank cell. Did try to replace Count with CountA but it does not work.

nrows=Worksheets("Master").Cell(Rows.Count,4).End(xlUp).Row
Worksheets ("Copy").Rows("2").copy
Worksheets ("Copy").Rows("3:"&nrows).Select
ActiveSheet.Paste

Thank you in advance for any help extended.
Cheers,
Ben

 

[Andrew Poulsom]

The value assigned to nrows is the row number of the last nonblank cell in column D on worksheet Master. What are you trying to do exactly?

 

[bquek]

The value assigned to nrows is the row number of the last nonblank cell in column D on worksheet Master. What are you trying to do exactly?

I want to copy row 2 in Sheet 2 based on the count in Sheet 1 (Master). Sheet 1 (Master) comprise of data abstract by formulas or links starting from row 4 from column A to O (column D & E manually input). If the code counted 10 rows (column A:A) having formulas/link/data in Sheet 1 (Master), then the code should copy row 2 in Sheet 2 and paste the subsequent rows under it (9 rows). Basically Sheet 2 is arranging the combination of data from Sheet 1(Master) based on a format for data uploading into another program.

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    nrows = Worksheets("Master").Cells(Rows.Count, 4).End(xlUp).Row - 3
    Worksheets("Copy").Rows("2").Copy Worksheets("Copy").Range("A3").Resize(nrows)
End Sub

 

[bquek]

Does this work for you?

Sub Test()
    nrows = Worksheets("Master").Cells(Rows.Count, 4).End(xlUp).Row - 3
    Worksheets("Copy").Rows("2").Copy Worksheets("Copy").Range("A3").Resize(nrows)
End Sub

Get Run Time Error: 1004 for second row code.

 

[bquek]

I tried again without -3 and it only copied 3 rows.

 

[Andrew Poulsom]

What is in column D on worksheet Master?

 

[bquek]

Worksheet Master Column D most of the time blank and occasionally manually input when disallowed sales tax incurred. Similarly with Column F.

 

[Andrew Poulsom]

Then maybe you should be using a column other than column D to set the nrows variable.

 

[bquek]

Then maybe you should be using a column other than column D to set the nrows variable.

Yes tested and change the (rows.Count,2)....Row -4. It works perfectly.