LinkBack URL
About LinkBacks
I want to use
to delete any row where column C contains a letter or 0201 or 0204. I have several that have like B289 or X954.
Please Login or Register to view this content.
I want to use
to delete any row where column C contains a letter or 0201 or 0204. I have several that have like B289 or X954.Please Login or Register to view this content.
Last edited by gz3s36; 06-05-2020 at 02:33 PM. Reason: I'm an idiot
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however you need to include code tags around your code.
Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I thought I did (#) .Alright I guess I don't remember how. I used #
Last edited by gz3s36; 06-05-2020 at 02:30 PM.
I suggest filter using a helper column...But as no sample file...to delete any row where column C contains a letter or 0201 or 0204
Please Login or Register to view this content.
Last edited by sintek; 06-06-2020 at 07:22 AM.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
@sintek Will this macro replace my really slow one?
Please Login or Register to view this content.
As I said in post 4...
But as no sample file...
Here is a very small sample. File actually has 106K rows.
perhaps...
Please Login or Register to view this content.
It didn't like the .AutoFilter 83, "T"
I've never heard of code not liking lol...
Here is another macro that you can try...
Please Login or Register to view this content.
Nevermind didn't address the tablature
Last edited by xladept; 06-07-2020 at 11:48 AM.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
See if this is faster.Originally Posted by gz3s36
Please Login or Register to view this content.
Hi ! Try this demonstration !
As I saw kids Excel beginners achieving the same just operating manually faster than some bad looping codes
so the easy way is to reproduce it, according to your post #7 attachment as a kid VBA beginner starter :
Please Login or Register to view this content.► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 06-06-2020 at 08:58 PM. Reason: oups ‼
Ignore my other post but try this:
Please Login or Register to view this content.
Re: Deleting Rows where column has a letter in it
Xladept,
SpecialCells is useless in your code - and can be a mess with big data like here ! - so remove it and try
Hi Marc L,
My code works nicely on the sample
Last edited by xladept; 06-07-2020 at 04:14 PM.
Re: Deleting Rows where column has a letter in it
Did I wrote your code not works ?‼
As I just wrote it can work without the useless SpecialCells method which is a mess with big data ! So try without and see the light
Well when I don't specify special cells it deletes the whole thing
Re: Deleting Rows where column has a letter in it
It's weird as Range.Copy like Delete methods works only with visible cells the reason why SpecialCells is useless
And because SpecialCells is size limited so not a great idea with big data !
I will test your code and revert in a while
Re: Deleting Rows where column has a letter in it
I just test and you are right, it can work without only on filtered cells but not on hidden cells not filtered.
But an issue with SpecialCells as size limited (areas #) with big data may be some rows to be deleted are not deleted
But over 2 billion non-contiguous cells are allowed in 32-bit configuration. That means that with 82 columns like this thread you're allowed about 243 thousand rows. It's been fun!
Seems the OP has given up anyway as he has not responded since post 10 LOL...
Re: Deleting Rows where column has a letter in it
For example the combination of an entire column and SpecialCells leads to a crash
- at least on my side with different computers and different Excel versions -
like with the post #11 code, the reason why it's better to avoid SpecialCells with big data !
According to post #15 code : it's maybe the slowest way used here as looping row by row to hide some rows takes ages !
The other slow way is even with rows hidden, whatever by a slow loop or via a filter,
deleting non continuous rows needs a while to be achieved !
As a reminder my post #14 code needs less than 6 seconds with 100K rows on a slow old laptop under Excel 2010.
This way was yet demonstrated on several threads of this forum
And operating this way even manually is faster than your code !
I tried yours with the 100K rows and after 3 minutes the procedure was still running
So just try at least with a set of 10K rows and compare the time execution of each code,
maybe on more recent versions than the ones I regularly use deleting rows is better optimized
@sintek No I haven't given up. I won't be back to work until tomorrow to work on this. Thanks for all the help. I will try this stuff tomorrow.
Re: Deleting Rows where column has a letter in it
Thanks to revert your tests
Here's a block hide - should be fairly quick:
Please Login or Register to view this content.
Re: Deleting Rows where column has a letter in it
Your last code is worse maybe 'cause of using Union so you did not even create any big table
According to post #7 attachment, duplicating DataBodyRange until rows # > 10K creates a 12K table (exactly 12 545 rows) :
on my side my post #14 Demo1 requires less than a second (exactly 0.656s),
your post #15 code needs 70.602s (so more than 100 times slower than Demo1 ) and
your post #27 code needs 191.742s (so ) then imagine the gap with the OP 110K rows data !
Last edited by Marc L; 06-08-2020 at 01:48 PM.
Hi Marc L,
Can you please post the 100K row Book that you're testing with or if not post it's generator?
Relativising time
As I had a doubt I ran again your post #15 code : it needs 142s
This is 'cause of your last code the old laptop computer becoming hotter & hotter its processor speed is decreased
but under the same condition Demo1 stays as fast
Important to notice : post #14 Demo1 & your post #15 code have the same result with 5 121 rows remaining
but your post #27 code more than 2 300 rows to be deleted are not !
Maybe there is something weird on my old laptop tests computer, the reason why often I ask to test on your side
Re: Deleting Rows where column has a letter in it
The 10K generator in a dirty style coding like if you wrote it !
Paste it to the worksheet module (or replace Me by the sheet reference) :
PHP Code:Sub Init10K()
Const B = "Button 2"
R& = Me.UsedRange.Rows.Count: If R > 9999 Then Exit Sub
Me.Shapes(B).Visible = False: [A1].Select
Application.ScreenUpdating = False
While R < 10000: V = Me.ListObjects(1).DataBodyRange.Value2
With Cells(R + 1, 1).Resize(UBound(V), UBound(V, 2)): .NumberFormat = "@": .Value2 = V: End With
R = Me.UsedRange.Rows.Count: Wend
Me.Shapes(B).Visible = True
Application.ScreenUpdating = True
End Sub
Right -
Re: Deleting Rows where column has a letter in it
I have to move now, see you later, I hope you saw post #30
I tried all suggestions but could not get any to work. Some would error and some just sat there and did nothing. Thank you all for your efforts.
You my friend....Are doing something wrong...All of the suggestions work with your supplied sample file...
I think it's something to do with the file. I use this code on another file but it won't work on this one. Same with all the help from this topic.
Please Login or Register to view this content.
What has that code you posted got to do with this thread????
It was just an example of code used elsewhere that won't work in this file. Thought it was evident with my last post. Sorry
This thread has to do with your initial requirement in post 1 and sample file in post 7 which multiple members have responded to with workable solutions...
Re: Deleting Rows where column has a letter in it
Try them first with your initial poor attachment !
As I replicated it to get a set of 100K rows, my post #14 Demo1 well works on it in less than 6 seconds
And operating this way manually you will need no more than a minute so faster than some slow VBA codes !
I ran the following code in my first sample near the top of this post and it works great. I tried it twice. Once in table form and once in Range. Worked both times.
But when I try to run it again from my original file (reduced to 50 rows it won't work). Here is the file it won't work in.Please Login or Register to view this content.
Re: Deleting Rows where column has a letter in it
When the attachment does very not reflect the real workbook that just means
the thread author is confident enough with its Excel / VBA skills to fit any code he may receive !
And when it's not the case it's so weird to join such poor attachment
I let the code author to answer you,
I just wait for once it will work how much time will be necessary to treat your real 110K rows workbook
as the more non contiguous rows to delete the slower with this kind of code
@Marc L...I ran this code on the last file I uploaded and it wouldn't run.
Please Login or Register to view this content.
Re: Deleting Rows where column has a letter in it
Yes like your previous post you just forgot to update cells addresses & column index,
just comparing with your post #7 attachment, at very beginner level
Last edited by Marc L; 06-09-2020 at 10:01 AM.
I found a way to combine both Marc L and Jindon code:
Please Login or Register to view this content.
Re: Deleting Rows where column has a letter in it
Why two formulas as only one is necessary ?‼
Still slow, did you test it at least with the 12K table as my Demo1 needs less than a second to delete the rows ?
And here on filtered cell SpecialCells is very useless like you can't see it in Jindon's code
Last edited by Marc L; 06-09-2020 at 11:26 AM.
(removed )
Last edited by Marc L; 06-09-2020 at 11:59 AM.
Re: Deleting Rows where column has a letter in it
Previous post was not a great idea even on only a 12K table,
freezed the old slow laptop becoming hotter & hotter, so to be forgotten
Re: Deleting Rows where column has a letter in it
xladept,
on my side on a 12K rows table your post #45 code is faster than your previous ones between 28 - 30s
- depends on hard disk cache, 0.3 - 0.4s less without SpecialCells as useless here on filtered cells -
so close to sintek post #10 code which is one second faster at cold at the first try with an empty hard disk cache
But these both codes with a 110K rows table may require around 5 minutes
as Demo1 way operated manually needs no more than a minute, around 7 seconds by code
As per post 41
You need to change the parameters for it to work as your new upload is not the same....But when I try to run it again from my original file (reduced to 50 rows it won't work). Here is the file it won't work in.
However, as per the DUEL going on between the members as to speed...Please Login or Register to view this content.
With the one who wins...Go you should...
Last edited by sintek; 06-09-2020 at 01:26 PM.
Re: Deleting Rows where column has a letter in it
Now the OP has posts #45 & 50 to test and revert the necessary time to operate his 110K rows table
Hey gz3s36...Just upload a sample file as per your actual row numbers to put the boys out of there misery lol...
Here is the file just as I receive it. I have done nothing to it Including not changing to Range from table).
What happened to Post 7 comment...
File actually has 106K rows.
Anyway after all of this...Initial code in Post 4 with column change would suffice...What a ride this was hey guys...Yihaa...Gidee Up
Last edited by sintek; 06-09-2020 at 02:28 PM.
Re: Deleting Rows where column has a letter in it
Yes for such small data worksheet the classic filter & delete way is fast enough !
Was not a duel but just a reminder for a 'well known' way to follow with big data
And...Seems the thread has died...All that work and no feedback...
Not gonna get much future help Member gz3s36 if you don't close off threads...
Sintek...I get it. Unfortunately I was pulled to the floor to help resolve an issue that was affecting production. Sintel/Marc L....When I wrote "Here is the file just as I receive it. (I have done nothing to it Including not changing to Range from table)." I was referring to not making any changes to cells, columns, rows or running other macros prior to posting. Of course I had to shorten it to post. The file is 31.9MB. I would be happy to post all 106K rows if I knew how. Thank you everyone on this post for your help. It really is appreciated.
Marc, I see you are using XL2003 and XL2010. My guess is you have a problem with SpecialCells when using your version XL2003 and, if you have it, XL2007. In those versions, SpecialCells has a limit of 8192 areas (non-contiguous ranges) no matter how many cells are in each of the areas... that limit was lifted in XL2010 and later (so the OP should have no trouble with the code I posted in Message #11 since he is using XL2013). For those using versions prior to XL2010, here is the code I posted in Message #11 modified to work around the area limit for SpecialCells in those versions...
Please Login or Register to view this content.
Last edited by Rick Rothstein; 06-12-2020 at 12:58 PM.
Re: Deleting Rows where column has a letter in it
Yes Rick but when it directly crashed it was under 2010 version !
In another thread I got a similar issue with SpecialCells again under 2010 version
where some codes well worked on my side with the original book but not once I saved it !
Same issue on another computer, the reason why I try to avoid it
I am surprised that XL2010 is crashing for you with SpecialCells. In some 10 years of posting SpecialCells "solutions", you are the first who has ever mentioned it not working with XL2010 or above. Just out of curiosity, did the modified solution I posted in Message #58 work for you?
Re: Deleting Rows where column has a letter in it
No, same error 1004 whatever on my saved workbook or on the original post #7 attachment as it is
under Excel 2010 and if I just remove SpecialCells, no more error
I was like you - surprised - years ago when someone pointed some weird issues with SpecialCells
He demonstrated in a short time some of them but I didn't have the time to analyse his workbook.
And in a more recent time on a local forum an Excel old chap' crack after some threads where he warned about it
enumerated all the weird things he met using it, he took so much time to try to find any logic but without success,
he warned SpecialCells crashing on a computer may work on another with exactly the same workbook and same Excel version.
I met issues with SpecialCells but as I didn't have much time
- often in a hurry, not on my computer, version 2003 to 2013 - so I did the necessary without
Rick,
I just try your post #58 code under another tests computer under Excel 2003 with the post #7 attachment as it is :
no issue, it wells works I checked on computers with the 2010 version if a reference was missing : all is ok
So I have no more idea why it fails on some computers / Excel version but it's not a surprise on my side
I don't understand it either. As I said before, in more than 10 years or so of posting SpecialCells solutions, your computer is the first I have heard of that cannot run code with it in them. Thanks for test my code on other XL2003 computers.
Re: Deleting Rows where column has a letter in it
Sometimes I meet or hear this kind of issue and since years I try to avoid using it in a pro context.
I tried under 2010 version with the values rather than the vb constants : the same
It seems to be particular with the combination of xlConstants & xlLogical as with another parameter it works.
To keep in mind
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks