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

tarunrag98

New Member
Joined
Jun 6, 2022
Messages
5
Office Version
  1. 2021
Platform
  1. Windows
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.

1654517891701.png




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

1654518305885.png
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
Excel Formula:
=TEXT(G4,"0000")&H4
Then copy upward and downward

This assumes that Serial# is on 4 digits
 
Upvote 0
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
Excel Formula:
=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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Let's try with the following macro:
VBA Code:
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...
 
Upvote 0
Let's try with the following macro:
VBA Code:
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.

1655134206790.png







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.


1655134363268.png




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
 
Upvote 0
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:
VBA Code:
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"?
 
Upvote 0

Forum statistics

Threads
1,223,627
Messages
6,173,421
Members
452,514
Latest member
cjkelly15

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
Back
Top