Posts by ngowanlock

    Re: Delete a row containing specific text - working but slow on larger number of reco


    Fastest method I've come up with is doing the following by using filters, this achieves an instant result regardless the number of records not sure if its the nicest way to do it, but its works! This assumes no header row present.


    I have the following vba code I picked up on stack overflow sometime back, the macro deletes records where the number 1 is present in column A but once this encounters several hundred rows the speed is very slow can anyone suggest a faster method to achieve the same?



    credits for original macro this was derived from: stackoverflow.com/questions/27927042/find-and-delete-rows-where-cell-value-is-n-a

    I am trying to get it working so that I can have a list with duplicate prevention but at the same time have it where a data validation drop down list can be used. Any help appreciated :)


    I've created a simple example below which works perfectly for manual entry of what I'm trying to achieve:


    Example:


    Visible


    [TABLE="width: 397"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Ford

    [/td]


    [td]

    Mondeo

    [/td]


    [td]

    FordMondeo

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Ford

    [/td]


    [td]

    Focus

    [/td]


    [td]

    FordFocus

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Vauxhall

    [/td]


    [td]

    Astra

    [/td]


    [td]

    VauxhallAstra

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    Renault

    [/td]


    [td]

    Megane

    [/td]


    [td]

    RenaultMegane

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    Renault

    [/td]


    [td]

    Clio

    [/td]


    [td]

    RenaultClio

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    Ford

    [/td]


    [td]

    Focus

    [/td]


    [td]

    FordFocus

    [/td]


    [/tr]


    [/TABLE]


    Ford is a none permitted entry in A6 due to the validation formula.


    Same code showing data validation present in column A and cell formula in column C



    [TABLE="width: 434"]

    [tr]


    [td][/td]


    [td]

    A Cells containing Data validation custom formula

    [/td]


    [td][/td]


    [td]

    Cell formula

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    =COUNTIF(C:C,C1)=1

    [/td]


    [td]

    Mondeo

    [/td]


    [td]

    =A1&B1

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    =COUNTIF(C:C,C2)=1

    [/td]


    [td]

    Focus

    [/td]


    [td]

    =A2&B2

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    =COUNTIF(C:C,C3)=1

    [/td]


    [td]

    Astra

    [/td]


    [td]

    =A3&B3

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    =COUNTIF(C:C,C4)=1

    [/td]


    [td]

    Megane

    [/td]


    [td]

    =A4&B4

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    =COUNTIF(C:C,C5)=1

    [/td]


    [td]

    Clio

    [/td]


    [td]

    =A5&B5

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    =COUNTIF(C:C,C6)=1

    [/td]


    [td]

    Focus

    [/td]


    [td]

    =A6&B6

    [/td]


    [/tr]


    [/TABLE]



    What I wanted to do is have it so that the custom data validation formula used in column A is instead in VBA. Then 'Column A' should instead contain a drop down list using the Data Validation List allowing for selecting an option e.g. Ford, Vauxhall, Renault, . I already have an extensive set of drop down lists using indirect vlookups, so implementing the list selection drop down another way to do a list would not be ideal.


    I found a previous post which does something similar to what I'm looking for (http://www.ozgrid.com/forum/showthread.php?t=157244) but I think it needs the variable 'r' to carry the value to the formula like what I have in column C but when a change occurs in column A and then preventing entry to the Cell in column A.


    In my finished spreadsheet the solution also needs to be able to co-exist with another macro effecting the same cells which clears the values of other drop downs when primary ones are changed: