Avatar of gs1uk
gs1uk
 asked on

Delete a Row which has a Emplty Cell in the 1st Column

Hi Experts,

 I would like to delete a row if the 1st column cel is emply. As below if you see the Col A has number 4 Missing. So i want to delete the entire row even if the B cell has data.

A      B      C      D      E
1            x      y      z
2            x      y      z
3            x      y      z
            x      y      z
5            x      y      z
6            x      y      z


Thanks Experts
Microsoft Excel

Avatar of undefined
Last Comment
SiddharthRout

8/22/2022 - Mon
Eric Zwiekhorst

sub DelRow()
dim i , amnt as integer
amnt = ActiveSheet.UsedRange.Rows.Count


for i=2 to amnt
if Cells(i,1) ="" then cells(i,1).entirerow.delete
next i
end sub


kr Eric
MINDSUPERB

You may filter Column A by Blank, then Delete the filtered rows.

Sincerely,
Ed
Eric Zwiekhorst

If the empty cell stil contains spaces you might try this..

Sub DelRow()
Dim i, amnt As Integer
amnt = ActiveSheet.UsedRange.Rows.Count
For i = 2 To amnt
If Not IsNumeric(Cells(i, 1)) Then Cells(i, 1).EntireRow.Delete
Next i
End Sub


kr Eric
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
gs1uk

ASKER
Hi Zwiekhorst,

How do i run this

Sub DelRow()
Dim i, amnt As Integer
amnt = ActiveSheet.UsedRange.Rows.Count
For i = 2 To amnt
If Not IsNumeric(Cells(i, 1)) Then Cells(i, 1).EntireRow.Delete
Next i
End Sub
SiddharthRout

Eric: You might want to reverse the loop?

For i= amnt to 2 Step -1

gs1uk: A faster method and a shorter code.

Try this

Sub Sample()
    On Error Resume Next
    Columns(1).EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
End Sub

Open in new window


Sid
SiddharthRout

gs1uk: To run the code, copy the code above and paste it in a module.

A slight chnage to my code since you will be pasting it in a . Replace Sheet1 with the relevant sheet name.

Sub Sample()
    On Error Resume Next
    Sheets("Sheet1").Columns(1).EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
End Sub

Open in new window


Sid
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
gs1uk

ASKER
Hi Can you explain me this please. Sidharth, I never used macros before. Im using excel 2007.
SiddharthRout

Sure :)

Let's take it step by step.

Can you see the developer TAB in Excel?

Sid
gs1uk

ASKER
Hi Sid,

I tried to run your module but it just blinks and then nothing happens. If I use Zwiekhorst it runs but as ur said it loops so I need to know how to run ur script
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
gs1uk

ASKER
I renamed the Sheet1 to 1

Sub Sid()
    On Error Resume Next
    Sheets("1").Columns(1).EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
End Sub
SiddharthRout

May I see your workbook?

Sid
gs1uk

ASKER
Well i just deleted the original data and tried your script and it worked . But i have 13000 rows like this and the data is different from the below.

Can I PM you the sample

Product Name      Product Discription      Product Number      Hyperlink      Price
A      X      11666      http://XXXX      29
A      X      3439      http://XXXX      19
A      X      9684      http://XXXX      9
A      X      16661      http://XXXX      25
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SiddharthRout

>>>Can I PM you the sample

It is not allowed in EE. :)

It doesn't matter how many rows are there :) The code will delete all blank rows in Column 1

Sid
gs1uk

ASKER
Alright there you go Sid.. Dint know the T/C sorry :)
Sid.xlsx
SiddharthRout

I just tried this and it works for me. Sample File Attached.

Sub Sample()
    On Error Resume Next
    Sheets("1").Columns(1).EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
End Sub

Open in new window


Sid
Sid.xls
Your help has saved me hundreds of hours of internet surfing.
fblack61
gs1uk

ASKER
Hi Sid,

Weill your rite it works now on the sample file . But not on my original data. I have no idea why. Im using excel 2010.

When I run Zwiekhorst script half of my data is deleted along with the space. I have around 13,000 rows after I run Zwiekhorst script i get 7000 Rows and rerun 4000

I do have more emplty rows but not so much which will go down till 4000.

SiddharthRout

Ok. I have modified Eric's code. Please try this.

Sub DelRow()
    Dim i As Long, LastRow As Long
    
    Application.ScreenUpdating = False
    
    LastRow = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row
    
    For i = LastRow To 2 Step -1
        If Len(Trim(Range("A" & i).Value)) = 0 Then
            Sheets("1").Rows(i).EntireRow.Delete
        End If
    Next i
    
    Application.ScreenUpdating = True
End Sub

Open in new window


Sid
Eric Zwiekhorst

Guys,

I see the problem, when there are 2 blanks in two following rows one is missed by the code

Sub DelRow()
    Dim i As Long, LastRow As Long
   
    Application.ScreenUpdating = False
   
    LastRow = Sheets("1").Range("A" & Rows.Count).End(xlUp).Row
   
    For i = LastRow To 2 Step -1
        If Len(Trim(Range("A" & i).Value)) = 0 Then
            Sheets("1").Rows(i).EntireRow.Delete
            i=i+1 'add one to look at the row again to see if following was empty also..
        End If
    Next i
   
    Application.ScreenUpdating = True
End Sub


Kr

Eric
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SiddharthRout

Eric, No you don't need that extra line. The code that I gave in ID: 35068903 will do  :)

Sid

Eric Zwiekhorst

Hi Sid,

Yes, you're right when comming from beneeth the row shifting up is already checked...

Kr

Eric
ASKER CERTIFIED SOLUTION
sstampf

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
sstampf

My code will also work when your "last row" is the target to be deleted. Which means, if your last row has missing value in first column then too my code will work.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
gs1uk

ASKER
Thanks Everyone who worked for this SiddharthRout and Zwiekhorst. Apparently I used sstampf's script and it gave me the result in 20secs what I wanted.

But I would like to thanks Sid for your hardwork.
SiddharthRout

Glad to be of help :)

Sid