Check whether a column has values within a value set

[Nirbhay Sharma]

Hi guys. I am new to Excel VBA and macros. I need some help and guidance to perform the following tasks:

1) Check if a column has values (text) lying with a provided set of values (text)
2) Populate a column indicating the same for every row (true/false)
3) after that i need to copy some specific columns for the rows where the value is "FALSE" to another sheet.

I have done the following so far.
- using the following formula to get true/false populated in cells
=SUMPRODUCT(COUNTIF(G2,"*"&$L$2:$L$5&"*"))>0
here G2 is the cell where the value is populated; L2-L5 is the range of set of values

now i need help to copy the specific columns (say column A,C,E,G) for the rows where there is a value false in column H (plz see sample)

also is it possible to convert all of this into a macro or vba script to make the process automated and more efficient? if yes, can you guys plz help?

thanks in advance

 

[Luke M]

Hi Nirbhay, and welcome to the forum.

Here's your workbook with macros in it. I built two separate ones, a marking and a moving one. The marking one currently calls the second one, so it will do both at same time. I just made them separate in case you needed to do something else between marking and moving them. I put a couple comments in cells to explain things.

 

[Nirbhay Sharma]

hi luke

first of all thanks for your help. it has helped me to implement the macro in the actual data file as well. but i am facing an issue. the movedata() function is copying the whole data of sheet 1 into sheet 2 for all false values. it is not copying only the required columns. can u plz help me with a way to achieve that.

also to get around this issue, i did some research and now am using the following code:

Sub DeleteMultiColumns()
With Worksheets("Sheet2")
Columns("B:G").EntireColumn.Delete
Columns("C:D").EntireColumn.Delete
Columns("D:J").EntireColumn.Delete
Columns("E:Z").EntireColumn.Delete
End With
End Sub

this gives me only the required columns. but the issue is that when i am trying to run this macro from sheet 1 it deletes the column from sheet 1 and not sheet 2 despite the sheet already specified. can you help me with this also?

again thanks for your help so far.

 

[Marc L]

Hi,

read With statement VBA help, you forgot dot before properties …

Using an advanced filter, column H is not necessary and K1 cell blank :​

Sub Demo()
Application.ScreenUpdating = False
Sheet2.UsedRange.Clear

With Sheet1
    .[K2].Formula = "=SUMPRODUCT(COUNTIF(G2,""*""&$L$2:$L$" & .[L1].End(xlDown).Row & "&""*""))=0"

    With .Cells(1).CurrentRegion
         .AdvancedFilter xlFilterInPlace, .Parent.[K1:K2]
         Union(.Columns(1), .Columns(3), .Columns(5), .Columns(7)).Copy Sheet2.Cells(1)
    End With

    If .FilterMode Then .ShowAllData
    .[K2].Clear
End With

Sheet2.Activate
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Luke M]

Marc hit the answer already with your With statement issue. For the AdvFilter not copying right columns, I'm guessing that you're either clearing the headers out before copying, or you didn't setup the headers on Sheet 2. If you look in the file I uploaded, I put the headers of the columns I wanted to copy in Sheet 2. That way, the filter knows what to pull.

 

[Marc L]

Luke aimed it right : with destination columns headers, no worry !

Mix of Luke's way and mine (Sheet1 column H not necessary & K1 cell
blank, desired columns headers in Sheet2) :​

Sub Demo2()
Application.ScreenUpdating = False
Sheet2.UsedRange.Offset(1).Clear

With Sheet1
    .[K2].Formula = "=SUMPRODUCT(COUNTIF(G2,""*""&$L$2:$L$" & .[L1].End(xlDown).Row & "&""*""))=0"
    .Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, .[K1:K2], Sheet2.Cells(1).CurrentRegion
    .[K2].Clear
End With

Sheet2.Activate
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Nirbhay Sharma]

Thank you Luke and Marc for your help