1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to delete rows based on length of string without loop?

Discussion in 'VBA Macros' started by Chirag R Raval, Feb 23, 2018.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    493
    Dear Sir,

    I have a code that insert extra column at data last , which have evaluated as Ttrue or False
    based on length of string in particular column. , Auto filter & remove / delete rows , based values have on that extra helper column.

    Code (vb):

    Sub StringLengthDelRows()
    Dim rng1 As Range
    Dim rng2 As Range
    Set rng1 = Range("A1").CurrentRegion
    Set rng2 = Range(Cells(1, rng1.Columns.Count + 1), Cells(rng1.Rows.Count, rng1.Columns.Count + 1))
    ActiveSheet.AutoFilterMode = False
    With rng2
        .Formula = "=LEN(RC[-" & rng1.Columns.Count & "]:RC[-1])<5"
        .FormulaArray = .FormulaR1C1
        .Value = .Value
        .AutoFilter Field:=1, Criteria1:="TRUE"
        .EntireRow.Delete
    End With
    ActiveSheet.AutoFilterMode = False
    End Sub
     
    how to achieve same result without insert extra helper column result?
    code should target just required column.

    hope your little help.

    Regards,

    Chirag Raval
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,428
    Upload sample workbook.

    By the way, why the aversion to loop?
  3. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    Hi !
    You can use Evaluate for the formula within a variable for the result
    but in this case you will need a loop to scan elements of this variable …
    The reason why I prefer to use a temporary column …
    Chirag R Raval likes this.
  4. Chirag R Raval

    Chirag R Raval Member

    Messages:
    493
    Dear Sirs,

    Thanks for your reply.

    I have column "A", there are very simple & basic requiremnt ,
    2 types of charecter lengths data in it (Lengh (Digits) 5 or 6)
    says buyer no or customer no, shirting buy no have 6 digits & suiting have 5.
    i need , remailns only shirting buy. Nos. (6 digits) to process subtotals on various columns .

    There are mostly in database , about 5 to 6 thousands rows to process.

    I feel wnder that how excel process some tasks , say like select only visible cells, in one shot or in one flash, or say selct all objects on sheet in one flash, , or just bold text in range in one flash , or say instantly hide all rows which are not necessary ,after click apply filter or press ok button in autofilter. or says like just apply complex conditional formmatting on range in one flash.

    Its seems there are more fast method use by excel beyond loop in excel
    to perform such task.

    I have qustion in my mind that why we can't (select or without select) process that targeted rang/cells/row/columns seems conditionally complex in one flash/in one shot like excel use it's that flash method? even acceptable to select range, to do that if its faster then loop (Though ,select is not good habit in
    technically and in terms of VBA)

    I also try to process or delete rows as by find method, value like something & convert that founded cells in error, & by use of intersect method, delete or do many things on that intersected areas like deleting or formatting like a in flash..... but "like" operator massup my data on that time, so i feel hesitate to use , that time, if i can control that situation or precise in structure of conditions, , as not to conver important cells in error, then that method also give really goods result..

    I (& Also Most Users) also prefer loop for complex situations, but simply autofilter & deleting is faster then loop.

    i have just simple requiremet for just check cell have 5 digits or 6? if less then 6, delete entire row.. jut it..& for jut do it, using loop, is i feel its less appreciable approach.

    i alsoo like & appreciate any method (not necessary to autofilter) that can delete that less then 6 digits rows
    instantly..

    Apologize, if i make mistake somewhere to describes or in my understanding because of my lake of knowledge of VBA.

    & i will upload sample workbook soon as i get little time like i got time to right this whole long Essay .

    Hope there are some way or ways to do that..in one flash...

    Regards,

    Chirag Raval
    Last edited: Feb 23, 2018
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,428
    Fastest method to achieve your end result. Is to first commit entire range to variant array. Clear the original range.

    Check for # of rows that meet criteria using Evaluate, Application.Countif, or some other method. Create array with row dimension using that number.

    Then loop through original variant array, check if it meets condition. Fill the created array (increment counter as well).

    Once created array is filled, put it back into range. This typically takes less than a sec for around 50~100k records.

    Or using some other method to do all operation in memory.

    At any rate, 5~6k rows is nothing and most code should be able to handle it without issue.

    As I have asked. Upload sample workbook so I can clearly see how your data is structured.
    Chirag R Raval likes this.
  6. Chirag R Raval

    Chirag R Raval Member

    Messages:
    493
    Dear Sir,

    Thanks for spread focus on matter, & may be your are right, flash can be happen
    when use array formula (CSE) (Control + Shift + Enter) in helper column,
    & then auto filter & delete..

    I need to re-study your post no 5 method that how it can be construct or achieve?

    okay I attached my sample file here for your doing needful.

    hope there are some way found..

    Regards,

    Chirag Raval

    Attached Files:

  7. Chirag R Raval

    Chirag R Raval Member

    Messages:
    493
    Dear All

    I Found another code mentioned below.
    that says "Most efficient way to delete row with VBA" you can find this sentence on google & you can find at last...on that page


    Just pass it what you're looking for and the column to search in.
    You could also hard-code the column if you want.

    Code (vb):

    Sub DelRowFinWithoutLoopAufiltArr3ok()
    'Sub PurgeRandy()
       Call FindDelete("G", "*RITCO*")
    End Sub
    Public Sub FindDelete(sCOL As String, vSearch As Variant) 'Simple find and Delete
    Dim lLastRow As Integer
    Dim rng As Range
    Dim rngDelete As Range
        Range(sCOL & 1).Select
        [2:2].Insert
        [2:2] = "***"
        Range(sCOL & ":" & sCOL).Select
        With ActiveSheet
            .UsedRange
                lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
            Set rng = Range(sCOL & 2, Cells(lLastRow, sCOL))
                rng.AutoFilter Field:=1, Criteria1:=vSearch
            Set rngDelete = rng.SpecialCells(xlCellTypeVisible)
                rng.AutoFilter
                rngDelete.EntireRow.Delete
            .UsedRange
        End With
    End Sub
     

    some remarks on that page at last..

    (1)This routine finds all instances of vSearch in a column. No need to walk through each cell in the column to find each instance. –

    (2) It's just an example of how to use AutoFilter, which I've found is a much quicker routine than using a find while walking a range of cells. To each his own.

    (3) Auto filter is a great approach. I was pointing out the OP has multiple values to look at using for the row deletion, so your routine needs to be called multiple times.


    so ,

    Above code really do its job very nicely on text , but....how to achieve result on Numbers or some formula result like Len ("string to find")??

    additionally, how can we use like operator within it?

    hope there are some suitable and efficient way to do this..

    Regards,
    Chirag Raval
    Last edited: Feb 24, 2018
  8. Marc L

    Marc L Excel Ninja

    Messages:
    3,673

    Using SpecialCells is very useless as Delete works only with visible cells ‼

    And SpecialCells is vicious 'cause limited in size but without prompting …
    To forget with big data !

    Using a filter is faster than any loop on rows …
    Chirag R Raval likes this.
  9. Marc L

    Marc L Excel Ninja

    Messages:
    3,673

    When there are a huge lot of rows to be deleted (the more non continuous)
    faster way may not be a filter but an additional column to mark rows to keep /
    to delete with a sort and at end a clear of the block of all lines to be deleted
    and a final clear of the additional column …
    Chirag R Raval likes this.
  10. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    As a reminder to process data, an optimized VBA Excel code
    is at least 50 times slower than a beginner VBA Access code ! …

    A recent test on a local forum with huge data :
    • Optimized VBA Excel : more than 50 minutes.
    • Beginner VBA Access : around 1 minute.
    • Optimized VBA Access : less than 30 seconds !

    As Excel is not a database software …
    Chirag R Raval likes this.
  11. Chirag R Raval

    Chirag R Raval Member

    Messages:
    493
    Dear Sir @Marc L ,

    Thank you for your reply,

    My post no 7's code also added one helper column & I can not hesitate if this method give desired result... as fast as possible. definitely I agree with your post no 9.

    if I divert from Excel to Access, then I must be start from stretch there..

    request to study my uploaded sample workbook & hope something found..

    Regards,
    Chirag Raval
  12. Marc L

    Marc L Excel Ninja

    Messages:
    3,673

    As a reminder : the best code is the one you are able to understand
    but far above all the one you are able to maintain in case of any change …

    As your initial code seems to work,
    just add a clear for the temp column before ending
    and desactivate display at beggining (ScreenUpdating) …
    Chirag R Raval likes this.
  13. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    As in your explainations you mix opposite ways (!),
    from your post #6 attachment can you at least
    just explain clearly & simply the purpose, what are you expecting ?

    Why ?
    'cause for example with an advanced filter you don't ever need
    to delete any rows ! And so on …

    If your initial post code does the job, just apply my previous post.

    6 thousands rows is not big data, using a filter is far enough,
    you can ever keep the bad useless SpecialCells code !
    Chirag R Raval likes this.
  14. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    Just respecting Logic ‼

    As it is at very beginner level just using Excel inner features
    (in fact at child level logic !),
    before to write any codeline, just operate manually,
    ask yourself how can you achieve it without a code ?!

    As using a filter for numbers doesn't change anything
    as it is just about child level logic,
    just try manually a filter on that column for 5 digits numbers.
    Once achieved with success, your code will need less than 10 codelines …
    (I need only 5 with a filter, not an advanced filter, just a filter
    and without using any variable neither an additional column !)


    Searching any code on Web is a waste of time for those
    who are not able to undertand or to mod it !
    And as you have more chances to find a bad code,
    better is to try yourself manually and once succeed,
    redo it after activating Macro recorder in order to get your own code base …
    Post then this generated code on any forum
    in order to learn how to rearrange, to clean it …
    Chirag R Raval likes this.
  15. Chirag R Raval

    Chirag R Raval Member

    Messages:
    493
    Dear Sir @Marc L ,

    Thanks for your effort to cover all possible aspect to cover this thread meet result.

    sorry for not to explain (forgot), with my attachment in previous post that what I want to achieve.

    I re-attach with result what I want. & with also working code (modify as per your guide) (Screen Updating, I can delete extra last columns, no worry )

    can I hope ? how can revise post no 7's code if we wish as another approach for just option ? or from intersect method , how can be chive this result?

    Regards,
    Chirag Raval

    Attached Files:

  16. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    Revise a bad code ? No thanks, what a waste of time, just trash it !
    As here it is just a very beginner Excel purpose need and
    as it is faster to do it yourself than to waste time with a Web search …
    As any beginner must resolve this without the Web neither any forum !

    As a 10 years old little girl (daughter of friends who were at my home)
    gave me the logic solution in less than 5 seconds
    and laughing « too easy, are you kidding me ?!» she said,
    select your column and enter filter mode, see the operators …
    Tip of the little girl : what is the first 6 digits number ? …
    So just with warming a couple of neurons, filter solution is very easy !

    So once your result filter is correct, remove it and redo same filter
    but this time activating Macro recorder before :
    you will get your own free code base !
    Then post here this generated code in case of an optimization need.

    First rule : Think Excel Before VBA …
    Chirag R Raval likes this.
  17. p45cal

    p45cal Well-Known Member

    Messages:
    1,051
    Code (vb):
    Sub blah()
      ActiveSheet.Columns(1).AutoFilter Field:=1, Criteria1:="<100000"
      Intersect(ActiveSheet.AutoFilter.Range, ActiveSheet.AutoFilter.Range.Offset(1)).EntireRow.Delete
      ActiveSheet.Columns(1).AutoFilter
    End Sub
    but don't run it twice on the same sheet, you'll lose all your data!

    …unless, that is, you use bad useless .Specialcells:
    Code (vb):
    Sub blah2()
      On Error Resume Next
      ActiveSheet.Columns(1).AutoFilter Field:=1, Criteria1:="<100000"
      Intersect(ActiveSheet.AutoFilter.Range, ActiveSheet.AutoFilter.Range.Offset(1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      ActiveSheet.Columns(1).AutoFilter
    End Sub
    Last edited: Feb 24, 2018
    Chirag R Raval likes this.
  18. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    No need Intersect neither On Error neither SpecialCells
    and without any issue even if run twice or more :​
    Code (vb):
        With Cells(1).CurrentRegion.Columns(1)
            .AutoFilter 1, "<100000"
            .Offset(1).EntireRow.Delete
            .AutoFilter
        End With
    These 5 easy codelines works too without .Columns(1)

    Chirag, with your few data yet sorted,
    Filter & Delete well do the job instantly.
    As written in post #12, you can desactivate display during process …

    Better is learning Excel basics before attempting any VBA code.
    Chirag R Raval likes this.
  19. Chirag R Raval

    Chirag R Raval Member

    Messages:
    493
    Dear Sirs @Marc L & @p45cal ,
    Both's code do is job very nicly, i use <799999 in it & its works best ,
    It is reasonable to feel anger by Gurus , on just kiddy talks. & like search in whole univers for little things which already inheritate in excel inner basic , really feel sorry for take your valuable time for just simple autofilter matter.
    But actually my first try already on that simple atofilter but filter not take Criteria1:="######" so i just divert on other side..
    So i try to just test below code as intersect method used to delete rows, without Filter but use of loop. Below code is just sample code
    Code (vb):

    Sub Deleterows()
    Dim ws As Worksheet
    On Error Resume Next
    For Each ws In Worksheets
      With ws.UsedRange
      .Replace "*800**", "#N/A", xlWhole, , False
      .Replace "*.*", "#N/A", xlWhole, , False
      Intersect(.Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    End With
    Next
    End Sub
     
    i just want to use it on just simple number types column. but fail
    code works & delete all *800* series. so i want to use operators in it

    <, > =, ?, # but can not construct.
    I also want it to use "Like" Operator
    so i put this matter as a thread in this forum.
    hope you can understand
    Regards,
    Chirag Raval
  20. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    Can't be best !

    [​IMG]
    Chirag R Raval likes this.
  21. Chirag R Raval

    Chirag R Raval Member

    Messages:
    493
    Dear Sir @MarcL,

    Okay okay , sorry , filter for >100000,
    But how offset (1) work ? In this code?
    Offset (1) take only 1 position (1 row only ,in this case)
    next to stay on
    Row ,which have value >100000.

    If we just select filtered result for copy to another
    Sheet we can see simple paste ,also paste all
    Hidden rows .

    Pease also spread focus that why
    ###### not work in simple autofilter?
    We can use "like"operator in VBA way?

    Or we must use advanced filter for that?

    Just for future reference .

    Regards,

    Chirag Raval
  22. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    Did you try at least any code from p45cal or even mine ?
    If so, how could you write this ?!

    No, Offset(1) just works as written in VBA inner help - a must read - :
    it just shifts all range one row down.
    Just check what returns Address property for the initial range
    and for the Offset range …

    A good code does not use Select to copy a range …

    An advanced filter is useful to directly copy its result
    to another worksheet for example (as an export),
    faster than any loop (except post #5)

    As several times written in differents threads,
    Copy & Delete methods do not use hidden cells !

    A filter works like you can operate manually :
    so try it manually (just see its operators) before commiting a code.
    If you can't manually, you can't success with your code !

    Like operator can be used but cell by cell so within a loop
    so far less efficient than any Excel inner feature like a filter !
    (Except the advanced developer like explained in Chihiro's post #5.)
    Chirag R Raval likes this.
  23. Chirag R Raval

    Chirag R Raval Member

    Messages:
    493
    Dear Sir @Marcl,

    Super, if clots found in veins, scanner Machine can just
    Display it , can not remove it. Master Doctors take risk, & reach at problem,
    Remove problem & repair mess forever , & in programming,
    Experts on this form, one like you , do their jobs same like a doctor,
    If , though, user less know about problem, you reach at problem,
    Repair, with precise instruct , discussdiscuss with user, pointing towards, user's mistake, & solve the problem,
    & that solution, usefully forever for globglob, with , even additions as try to cover many
    Aspects of situations & conditionsconditions - why this solution apply on it.
    Just great job done by volunteers. Thanks forever.

    Just one point may be left about do job by , flashes byby, excelexcel
    If we command to excel select all objects on a sheet, it use loop to select each one by one?
    Because each object have own their index no.

    Matrix' s favourite dialog "If there start something then Definetly there are end of that"
    As respect excel, if object created on sheet definetly that created as order, and when there are order,
    They have index no, we can slect each by loop.
    How excel select objects in one flash? It use loop?.or something else beyond loop?
    How Ctrl shift enter do it job in one flash?

    Regards,

    Chirag Raval
  24. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    As all is upon objects, the question is « Which kind of objects ? »
    Select Ok but for doing what ? As a good code …

    As an object is a member of a collection,
    if the purpose really needs a loop so via For Each

    See also SelectAll method in VBA inner help.
    Chirag R Raval likes this.
  25. p45cal

    p45cal Well-Known Member

    Messages:
    1,051
    True enough. Well done!
    Chirag R Raval likes this.

Share This Page