VBA to filter out entire row on the value of a single cell in a column

[tarunrag98]

Hi guys,

I am relatively new to Excel and this is my first post here so please excuse any errors or stupidity on my part. I currently have two problems.

Firstly, I need to merge two columns that contain numbers. The problem is that when I do this it is deleting the zeros in the middle. I have posted a screenshot for your reference. As you can see from the pic, it is deleting the zeros at the starting of the serial number. So in cell I4, instead of getting 00431432520 I am getting 431432520 and so on. I have used ampersand, concatenate, formatting the column I so that it has to have 11 digits, etc but nothing has worked out so far. Any VBA suggestions are also welcome.

Secondly, I have a dataset from which I need to filter out values. The data range is from Column A to Column AA. In columns V to AA I have some vlookups that are returning the value of #N/A. I need the VBA to work like

1. In column V whichever cell has #N/A, filter that entire row from A to AA, copy and paste into another worksheet/workbook
2. This is to be applied to all columns from V to AA that have the vlookup. It does not matter if the same row is filtered out multiple times. I have attached a screenshot for reference.

Thank you very much

 

[Anthony47]

I suspect that G4 contains the number 43, not the string 0043, and you read 0043 due to formatting.
If this is your case, try using in I4

=TEXT(G4,"0000")&H4

Then copy upward and downward

This assumes that Serial# is on 4 digits

 

[tarunrag98]

I suspect that G4 contains the number 43, not the string 0043, and you read 0043 due to formatting.
If this is your case, try using in I4

=TEXT(G4,"0000")&H4

Then copy upward and downward

This assumes that Serial# is on 4 digits

Thanks for the solution. Please help on the second problem if possible.

 

[Anthony47]

As far as the second question:
1. In column V whichever cell has #N/A, filter that entire row from A to AA, copy and paste into another worksheet/workbook
2. This is to be applied to all columns from V to AA that have the vlookup. It does not matter if the same row is filtered out multiple times. I have attached a screenshot for reference.
Don't understand what you mean for "filter the entire row", and "This is to be applied to all columns from V to AA that have the vlookup"

Is it correct that you would like to check each row in the range; if there is one or more errors then copy that row to Worksheet(xyz)? If No, then please explain again

 

[tarunrag98]

As far as the second question:
1. In column V whichever cell has #N/A, filter that entire row from A to AA, copy and paste into another worksheet/workbook
2. This is to be applied to all columns from V to AA that have the vlookup. It does not matter if the same row is filtered out multiple times. I have attached a screenshot for reference.
Don't understand what you mean for "filter the entire row", and "This is to be applied to all columns from V to AA that have the vlookup"

Is it correct that you would like to check each row in the range; if there is one or more errors then copy that row to Worksheet(xyz)? If No, then please explain again

Hi,

Apologies for the late response.

For example, I need to check each cell in the column V. Suppose in cell V4 there is #N/A, then I want to copy the entire row 4 from A to AA and paste (without formulas) it in another workbook and delete the entire row from the original workbook. This is to be done for columns W, X, Y, Z and AA. It does not matter if the same row is copy pasted multiple times due #N/A in all the columns from V to AA. For example as per this procedure, row 2 has to be copy-pasted 4 times since the cells V2, W2, X2 and Z2 all have #N/A.

If copying and pasting is not possible, I would atleast like the filtered data to appear in the original worksheet itself and I will copy paste manually.

Thank you

 

[Anthony47]

Let's try with the following macro:

Sub ErroredRows()
Dim dSh As Worksheet, oSh As Worksheet
Dim I As Long, NextR As Long, kRow As Boolean

Set oSh = Sheets("Output")      '<<< The sheet to copy to
Set dSh = Sheets("Sheet1")      '<<< The sheet to be tested

dSh.Select
On Error Resume Next
NextR = oSh.Range("V:AA").Find(What:="*", After:=oSh.Range("V1"), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row + 1
On Error GoTo 0
For I = Cells(Rows.Count, "V").End(xlUp).Row To 2 Step -1
    kRow = False
    For J = 22 To 27        'Columns V to AA
        If IsError(Cells(I, J)) Then
            oSh.Cells(NextR, "A").Resize(1, 27).Value = Cells(I, "A").Resize(1, 27).Value
            NextR = NextR + 1
            kRow = True
        End If
    Next J
    If kRow Then
        Cells(I, 1).EntireRow.Delete
        kRow = False
    End If
Next I
End Sub

Copy the code into a standard module of your vba project; the two lines marked <<< have to be costomized with your information.

This will search from bottom of your data up to row 2, and will check one by one columns V:AA; in case the cell is in error then columns A:AA of the row will be copied to a second worksheet and, at the end of the checks, the line will be deleted.

Hope this is what you meant...

 

[tarunrag98]

Let's try with the following macro:

Sub ErroredRows()
Dim dSh As Worksheet, oSh As Worksheet
Dim I As Long, NextR As Long, kRow As Boolean

Set oSh = Sheets("Output")      '<<< The sheet to copy to
Set dSh = Sheets("Sheet1")      '<<< The sheet to be tested

dSh.Select
On Error Resume Next
NextR = oSh.Range("V:AA").Find(What:="*", After:=oSh.Range("V1"), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row + 1
On Error GoTo 0
For I = Cells(Rows.Count, "V").End(xlUp).Row To 2 Step -1
    kRow = False
    For J = 22 To 27        'Columns V to AA
        If IsError(Cells(I, J)) Then
            [COLOR=rgb(250, 197, 28)]oSh.Cells(NextR, "A").Resize(1, 27).Value = Cells(I, "A").Resize(1, 27).Value[/COLOR]
            NextR = NextR + 1
            kRow = True
        End If
    Next J
    If kRow Then
        Cells(I, 1).EntireRow.Delete
        kRow = False
    End If
Next I
End Sub

Copy the code into a standard module of your vba project; the two lines marked <<< have to be costomized with your information.

This will search from bottom of your data up to row 2, and will check one by one columns V:AA; in case the cell is in error then columns A:AA of the row will be copied to a second worksheet and, at the end of the checks, the line will be deleted.

Hope this is what you meant...

Hi,

I am getting this error and it is highlighting the line in yellow in your code. I have tried toggling the options in the Trust Center as advised on the Internet but nothing has happened.

Additionally, I have written some code for this button but I am getting this error and it is highlighting the line in yellow in my code. Please advise.

Private Sub CommandButton5_Click()


Workbooks.Add
ActiveWorkbook.SaveAs "C:\Users\Raghuram Tarun\Desktop\Mantis Final.xlsx"
Sheets.Add
ActiveSheet.Name = "ldbored"
ActiveSheet.Name = "Sheet2"
ActiveSheet.Name = "Sheet3"
ActiveSheet.Name = "Sheet4"
ActiveSheet.Name = "Sheet5"
ActiveSheet.Name = "Sheet6"



Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=22, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test 2 - Copy").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("ldbored").Range("A:AA").PasteSpecial Paste:=xlPasteValues

Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=23, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test 2 - Copy").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("Sheet2").Range("A:AA").PasteSpecial Paste:=xlPasteValues

Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=24, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test 2 - Copy").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("Sheet3").Range("A:AA").PasteSpecial Paste:=xlPasteValues

Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=25, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test 2 - Copy").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("Sheet4").Range("A:AA").PasteSpecial Paste:=xlPasteValues

Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=26, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test 2 - Copy").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("Sheet5").Range("A:AA").PasteSpecial Paste:=xlPasteValues

Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter Field:=27, Criteria1:="#N/A"
Workbooks("Mantis WMS Template - Test 2 - Copy").Worksheets("MaterialSalesOrders").Range("A:AA").Copy
Workbooks("Mantis Final.xlsx").Worksheets("Sheet6").Range("A:AA").PasteSpecial Paste:=xlPasteValues

End Sub

 

[Anthony47]

As far as the Error 1004, this will happen if the destination sheet is completely blank
Replace the initial

On Error Resume Next
NextR = oSh.Range("V:AA").Find(What:="*", After:=oSh.Range("V1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row + 1
On Error GoTo 0

with this:

On Error Resume Next
NextR = oSh.Range("V:AA").Find(What:="*", After:=oSh.Range("V1"), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
On Error GoTo 0
NextR = NextR + 1

As far as the new problem, I think you should discuss a single problem in a sigle thread, or the forum will be useless for the other users.

Anyway, keep in mind that when the instruction Worksheets("MaterialSalesOrders").Range("A:AA").AutoFilter etc etc is executed the active workbook is the newly created one, saved as Mantis Final.xlsx, and I don't think it has a sheet named "MaterialSalesOrders". Try using ThisWorkbook.Worksheets("MaterialSalesOrders").Range("A:AA") etc etc
But if this doesn't work please consider opening a new thread

PS: why do you rename the same ActiveSheet "ldbored", then "Sheet2", . . . then "Sheet6"?