Solved

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

Posted on 2011-03-08
211 Views
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
0
Question by:gs1uk
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 9
• 8
• 4
• +2

LVL 6

Expert Comment

ID: 35068272
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
0

LVL 19

Expert Comment

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

Sincerely,
Ed
0

LVL 6

Expert Comment

ID: 35068318
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
0

Author Comment

ID: 35068402
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
0

LVL 30

Expert Comment

ID: 35068406
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
``````

Sid
0

LVL 30

Expert Comment

ID: 35068417
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
``````

Sid
0

Author Comment

ID: 35068563
Hi Can you explain me this please. Sidharth, I never used macros before. Im using excel 2007.
0

LVL 30

Expert Comment

ID: 35068581
Sure :)

Let's take it step by step.

Can you see the developer TAB in Excel?

Sid
0

Author Comment

ID: 35068640
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
0

Author Comment

ID: 35068643
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
0

LVL 30

Expert Comment

ID: 35068653
May I see your workbook?

Sid
0

Author Comment

ID: 35068705
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
0

LVL 30

Expert Comment

ID: 35068721
>>>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
0

Author Comment

ID: 35068738
Alright there you go Sid.. Dint know the T/C sorry :)
Sid.xlsx
0

LVL 30

Expert Comment

ID: 35068769
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
``````

Sid
Sid.xls
0

Author Comment

ID: 35068849
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.

0

LVL 30

Expert Comment

ID: 35068903
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
``````

Sid
0

LVL 6

Expert Comment

ID: 35069125
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
0

LVL 30

Expert Comment

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

Sid

0

LVL 6

Expert Comment

ID: 35069155
Hi Sid,

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

Kr

Eric
0

LVL 12

Accepted Solution

sstampf earned 500 total points
ID: 35069178
You can try this code. It should work faster. To run this code just activate your desired sheet and run the macro. Let me know if you have any questions/concerns.
``````Sub test()
Application.ScreenUpdating = False
Dim rng As Range
Dim lstRow As Long
lstRow = ActiveSheet.UsedRange.Rows.Count
Set rng = Cells(lstRow + 1, 1).EntireRow
For i = 1 To lstRow
If Trim(Cells(i, 1).Value) = "" Then
Set rng = Union(rng, Cells(i, 1).EntireRow)
End If
Next i
rng.Delete
Set rng = Nothing
Application.ScreenUpdating = True
End Sub
``````
0

LVL 12

Expert Comment

ID: 35069206
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.
0

Author Comment

ID: 35069555
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.
0

LVL 30

Expert Comment

ID: 35069572
Glad to be of help :)

Sid
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦

#### 733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.