VBA code not recognising all filled rows

Absfml

New Member
Joined
Mar 5, 2019
Messages
2
Afternoon
First post so please bear with me, and a bit of a beginner with VBA codes.

I'm trying to use a VBA code to identify all rows on a worksheet that contain the word 'No' in a certain column, then copy and paste these rows into a separate worksheet. This worked with the initial set of data on the master worksheet, but this data is getting added to every month, and now the code is not picking up all the rows required. I have 20 rows that show 'No' in the specified column, but only 10 of these are transferring to the new worksheet. It's not that the code isn't reading the newly added data, as some of this is showing correctly once the codes run.

Would really welcome any suggestions. This is what I'm using:
Code:
Private Sub CommandButton1_Click()




Worksheets("Failed QC's").Activate
Worksheets("Failed QC's").Range("A2:DF1500").ClearContents




a = Worksheets("Master form").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a




Worksheets("Master form").Activate


If Worksheets("Master form").Cells(i, 92).Value = "No" Then


Worksheets("Master Form").Rows(i).Copy
Worksheets("Failed QC's").Activate
b = Worksheets("Failed QC's").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Failed QC's").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Master form").Activate


End If
Next


Application.CutCopyMode = False
ThisWorkbook.Worksheets("Master form").Cells(1, 1).Select


End Sub
 
Last edited by a moderator:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,795
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the forum.

Is there always data in column A for each row?

You could do it with an autofilter, but if you want to loop, perhaps this:

Code:
Private Sub CommandButton1_Click()

    Worksheets("Failed QC's").Range("A2:DF1500").ClearContents
    Dim outRow As Long
    outRow = 2

    With Worksheets("Master form")
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        Dim i As Long
        For i = 2 To lastRow

            If UCase$(.Cells(i, 92).Value) = "NO" Then

                .Rows(i).Copy Worksheets("Failed QC's").Cells(outRow, 1)
                outRow = outRow + 1

            End If
        Next

        Application.Goto .Cells(1, 1), True
    End With

End Sub
 
Last edited:

Absfml

New Member
Joined
Mar 5, 2019
Messages
2
Thankyou! The first 3 columns are normally hidden as the data is irrelevant but on checking blanks which explains it.
Will try with your code though- thanks again- very helpful.
 
Last edited by a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,795
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
That's probably the issue then as your code (and mine) is using column A to determine the last row of data. Change it to use column D, or another column that is always populated.
 

Forum statistics

Threads
1,147,995
Messages
5,744,233
Members
423,854
Latest member
jaiprakashrao1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top