Delete Entire Row If Cell Is Empty 1

[risk99]

Hi,

I have data in three columns, and want to delete the entire row if the cell on column three is empty, and shift everything up.

My aproach is to select all the data in column three, and count the number of rows, then create a "for" loop to delete the rows.

Please help! Thank you!!

 

[PHV]

A starting point:
With ActiveSheet.UsedRange
.AutoFilter
.AutoFilter Field:=3, Criteria1:="="
Range("A2:C65536").SpecialCells(xlCellTypeVisible).EntireRow.Delete Shift:=xlUp
.AutoFilter Field:=3
End With


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Why not simply ..

[blue]Range("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[risk99]

Thanks for all the quick reply, I've tried this, but it didn't work, please let me know what's wrong. The error is in the line with Range("i:i").....

Option Base 1

Sub delete_row()

Dim i As Integer, n As Integer

n = Selection.Rows.Count

For i = 1 To n

Range("i:i").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Selection.Delete Shift:=xlUp

Next i

End Sub

 

[SkipVought]

Sub delete_row()
Range("i:i").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[risk99]

Hi Skip,

Can you be more specific? I'm still having dificulties to run the code. Thanks!

 

[SkipVought]

Why? What is the difficulty? It's a single statement not a loop.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[risk99]

Ok, let's say I have my data from B1:B20, and I select the range, then run the macro. But, it doesn't work. Thanks!

 

[Loomah]

I see the problem!!!

risk99
The code Tony posted refers to the range Range("C:C")
This is a single column range and not referenced by means of any variable. It could just as easily be referred to as
Columns("C") or
Columns(3) or
Range("C1:C65536")

What you appear to be trying to do is reference the range using a variable that you are increasing within a loop.

There is no need for a loop if you use the code Tony posted. Additionally, there is absolutely no need to select any specific cells. All you need to do is adapt the code to show the column letter that you are interested in checking, though that shouldn't be necessary as you specified column 3 which just happens to be Range("C:C")!

Coincidence? Wouldn't have thought so!!

Try the code precisely as it was originally posted.

One major advantage of using this method is that it is massively faster than looping through the cells, especially when you have tens of thousands of rows to check.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[risk99]

Loomah, THANK YOU VERY MUCH!!!