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.
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?
Range("C:C").SpecialCells(xlBlanks).EntireRow.Delete
How do I select all filtered rows via VBA?
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 tooSub delete_if_blank_in_colC() Dim r As Long, c As Long r = Cells.Find("*", , , , xlByRows, xlPrevious).Row c = Cells.Find("*", , , , xlByColumns, xlPrevious).Column With Range("A1").Resize(r, c) If Application.CountBlank(.Columns(3)) > 0 Then .Resize(, 1).Offset(, c) = Evaluate("if(" & .Columns(3).Address & "="""",1,"""")") .Resize(, c + 1).Sort .Cells(1, c + 1), 1, Header:=xlNo .Range(.Cells(1, 1), .Cells(Rows.Count, c + 1).End(3)).Delete xlUp Else MsgBox "No blank cells in col C" End If End With End Sub
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.
CheersSub Delete_Rows() Dim lr As Long, i As Long lr = Cells(Rows.Count, 3).End(xlUp).Row For i = lr To 1 Step -1 If Cells(i, 3).Value = "" Then Cells(i, 3).EntireRow.Delete End If Next i lr = Cells(Rows.Count, 1).End(xlUp).Row End Sub
RandomPezzer -- your solution worked perfectly.
Kalak,
That code just deleted everything on my sheet...
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