LinkBack URL
About LinkBacks
Hello,
I cannot find a solution online that works.
If there are any blanks found in Column C, I want it to delete that entire row.
However, these blank cells may not be adjacent to one another.
Hello,
I cannot find a solution online that works.
If there are any blanks found in Column C, I want it to delete that entire row.
However, these blank cells may not be adjacent to one another.
Hi,
The fastest way I know to do this is to use Autofilter for a blank on column C then select all he filtered rows and delete them.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
How do I select all filtered rows via VBA?
I did exactly as you suggested via VBA, it is the actual deletion part I am stuck on.
have you tried just running the single-line code?
Please Login or Register to view this content.
When I use the one-line I get Run-time error '1004': Delete method of Range class failed.
I have non-adjacent rows.
That 1004 error happens when there are no blank cells in the range of interest
You can leave it as it is for an indicator that you don't have blank cells, but this is rarely done
more usual is to include a line
On Error Resume Next
above the one line I gave
or, if one wants to preserve the "one-liner" property then a conditional statement such as
If Countblank(Range of interest) > 0 then ... (the one-line as posted above) can be used.
If you've got lots of rows, say 100,000 or half a million or so and you want it done fast, then you can use a code likethis should be OK for smaller number of rows tooPlease Login or Register to view this content.
Last edited by kalak; 09-16-2014 at 04:50 PM.
Hi There,
I use this code for when i need to delete rows if empty or contains a set value.
CheersPlease Login or Register to view this content.
Kalak,
That code just deleted everything on my sheet...
RandomPezzer -- your solution worked perfectly.
Just to tidy up. Did you actually use the code that's now in Post#7?Originally Posted by EnigmaMatter
I'd modified an initial version shortly after posting. The initial version could have done as you say, which is why I changed it.
It's very hard to see how the version now in Post#7 could have done this. It certainly didn't on any and all test data that I tried.
I may have used the initial code.
Thank You,
EnigmaMatter
Thanks for the feedback. That's always useful and encouraging to get.
The RandomPezzer type of code is actually very widely used (and generally reliable) for this problem so I guess I was surprised you didn't find something like it online and were maybe looking for something different.
The second last line is redundant, and if for any reason it's used instead of the earlier lr = ... then sometimes can get the wrong result.
Well, I tend to find excellent VBA Subs and code on Google. I tend to find answers to many questions that way. However, this forum in particular tends to provide answers are better than what I find through search for.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks