TroubleShoot Delete Row Macro...

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
751
Office Version
  1. 365
Platform
  1. Windows
I have this Macro that if a particular column has a an empty cell then it should delete the entire row...

It works BUT it limited to the number of rows it deletes at a given time (meaning I need to keep re-running this macro)

i cant pinpoint the reason it stops...i double check each column and even highlighted the empty columns and deleted (incase of a . or anything else I might of missed and it stills doesnt run 100% thorugh the list at 1 time)
here is the macro


----------------------------
Sub deleteemptyrow()

Dim c As Range
Range("a2").Select
For Each c In Range("b1:b" & Range("b65536").End(xlUp).Row)
If c.Value = "" Then
'MsgBox ("nothing")
c.Offset(0, 0).EntireRow.Delete
End If
Next c
'Exit For
End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
The reason that happens is ..

For each C in Range...
That goes from Top to Bottom Row 1 first, then row 2..etc..)
When the criteria is met and a row is deleted..say it deleted row 7...
Whatever was in row 8 is now in row 7.
Whatever was in row 9 is now in row 8.
Then the loop goes to Row 8 and checks row 8.
That effectively skipped row 8.

so what you do to resolve is to make it go backwards, from the bottom up..

try like this
Code:
Sub deleteemptyrow()
 
Dim c As Long
 
For c = Range("B65536").End(xlup).Row to 1 Step -1
    If Cells(c,"B").Value = "" Then
        'MsgBox ("nothing")
        Rows(c).EntireRow.Delete
    End If
Next c
End Sub

However, an even better more efficient way to do it is to do it without a loop...

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


Hope this helps..
 
Upvote 0
very easy solution to this problem, thanks for the explanation, makes complete sense

quick question....is there I can create a box that pops open before the macro runs and asks me what column to check? then upon "ok" it runs the macro based on the column value i inserted into the box?

thanks
 
Upvote 0
With this you'll be able to SELECT the column with your mouse (or type it like C:C)

Code:
Dim MyRange as Range
Set MyRange = Application.Inputbox(Prompt:="Select Column",Type:=8)
MyRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
Upvote 0
very wierd, it works on some workbooks but not others...

for some reason this is giving me an error.. error of "run time error, no cells were found"
but i am staring at the empty cells and highlighted the column just like i did on the other workbooks that it did work!

any ideas on why?

the debugger highlights...MyRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Sub DeletEmptyCells()
Dim MyRange As Range
Set MyRange = Application.InputBox(Prompt:="Select Column", Type:=8)
MyRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub
 
Upvote 0
Yes it will error if there are no blank cells found..

you can replace that line with this
Code:
On Error Resume Next
MyRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error Goto 0

But if you think there are blanks, but it errors, then your blanks are probabaly not really blanks...
Check the data for Spaces in the cell.
Also, formulas returning blank will not be counted as blank..
 
Upvote 0
thanks for replying but there are blank cells, my last post "and Im staring right at the blank cells"

the column has 36000 rows...could that be it?

here is a sample that i just tried....select column B

Gittus, Richard JK INVESTMENT MANAGEMENT LLP Guildford
Banerjee, Sam JK INVESTMENT MANAGEMENT LLP Guildford
Bell, John SCHRODERS PRIVATE BANK Guernsey
Warywoda, Mark CO OPERATORS INV COUNSELLING Guelph
Gonzaga, Cesar BANCO DE GUAYAQUIL Guayaquil
Urbain, Claude Pierre Guatemala City
Zhu, J GUANDONG ERICSSON TELECOM ENG Guangzhou China 510665
Prakash, Om PANIMEX Guangzhou Guangdong
Daswani, Arun Guangzhou
Thiebaud, Xavier Guangzhou
Sirsalewal, Om Prakash PANIMEX TRADING PTE LTD Guangzhou
Aranas, Jose L HUA MEI SANITARY WARE CO LTD Guangdong Province
Brockes, Klaus NESTLE DONGGUAN LTD Guangdong Province
Brugger, D Charles Guangdong
Tang, William DAYU TRADING Guangdong
Li, Zhengjia INSIGHT INTERNATION HOLDING CO Guang Dong
Wittenberg, Pieter TPG KPN PENSIOEN BV Groningen
Luning, Coos TPG KPN PENSIOEN BV Groningen
Van Wijk, Roelie TPG KPN PENSIOEN BV Groningen
De Boer, Arjen TPG KPN PENSIOEN BV Groningen
Kramer, Anton TPG KPN PENSIOEN BV Groningen
Wilken, Oldrik TPG KPN PENSIOEN BV Groningen
Mcteague, Peter PROLOGUE CAPITAL Greewich
Eastland, Woods E STAPLCOTN Greenwood
Taylor, Peter Greenwich 2065 Nsw
Dettmer, Robert Greenwich
Gibb, Allan Greenwich
Harris, David ACUITY CAPITAL MANAGEMENT Greenwich
Ellsworth, William ADMIRAL CAPITAL MANAGEMENT Greenwich
Puckowitz, Stephen ADMIRAL CAPITAL MANAGEMENT Greenwich
Li, Dan ADMIRAL CAPITAL MANAGEMENT Greenwich
Bloomer, Jeremy AIRLIE OPPORTUNITY CAP MGMT Greenwich
 
Upvote 0
i select the cell/cells that are empty and there is nothing in the formula bar...

the format type is TEXT, i hit "delete" any way and run the macro and it does delete those particular rows...what is Excel recognizing in those cells?

it is supposed to be a company name in that column....if it is blank it just means that those are individuals and no company associated with it, there is no formula that is run on any cell in this sheet, its just a downloaded data file...but excel somehow registers something in the cell....

should i save it in some particular format? any ideas? thanks for helping....
 
Upvote 0
If you pressed delete in the cell, then the macro works, then that means there is SOMETHING in that cell.

Likely a Space...

You can use =CODE(A1) to verify that, if it returns a number, then it's not blank.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top