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

[Chirag R Raval]

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.

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

 

[Chihiro]

Upload sample workbook.

By the way, why the aversion to loop?

 

[Marc L]

Hi !

how to achieve same result without insert extra helper column result?

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]

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

 

[Chihiro]

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]

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

 

[Chirag R Raval]

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.

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

 

[Marc L]

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 …
​

 

[Marc L]

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 …
​

 

[Marc L]

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]

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

 

[Marc L]

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) …
​

 

[Marc L]

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 !
​

 

[Marc L]

how to achieve result on Numbers or some formula result like Len ("string to find")??

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]

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

 

[Marc L]

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 …
​

 

[p45cal]

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:

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

 

[Marc L]

No need Intersect neither On Error neither SpecialCells
and without any issue even if run twice or more :​

    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]

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

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

 

[Marc L]

i use <799999 in it & its works best

Can't be best !

Tip of the little girl : what is the first 6 digits number ? …

 

[Chirag R Raval]

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

 

[Marc L]

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]

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

 

[Marc L]

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.
​

 

[p45cal]

No need Intersect neither On Error neither SpecialCells and without any issue even if run twice or more

True enough. Well done!