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
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
You may filter Column A by Blank, then Delete the filtered rows.
Sincerely,
Ed
Sincerely,
Ed
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
Sub DelRow()
Dim i, amnt As Integer
amnt = ActiveSheet.UsedRange.Rows
For i = 2 To amnt
If Not IsNumeric(Cells(i, 1)) Then Cells(i, 1).EntireRow.Delete
Next i
End Sub
kr Eric
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
How do i run this
Sub DelRow()
Dim i, amnt As Integer
amnt = ActiveSheet.UsedRange.Rows
For i = 2 To amnt
If Not IsNumeric(Cells(i, 1)) Then Cells(i, 1).EntireRow.Delete
Next i
End Sub
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
Sid
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
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.
Sid
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
ASKER
Hi Can you explain me this please. Sidharth, I never used macros before. Im using excel 2007.
Sure :)
Let's take it step by step.
Can you see the developer TAB in Excel?
Sid
Let's take it step by step.
Can you see the developer TAB in Excel?
Sid
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
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
ASKER
I renamed the Sheet1 to 1
Sub Sid()
On Error Resume Next
Sheets("1").Columns(1).Ent ireRow.Spe cialCells( xlBlanks). EntireRow. Delete
On Error GoTo 0
End Sub
Sub Sid()
On Error Resume Next
Sheets("1").Columns(1).Ent
On Error GoTo 0
End Sub
May I see your workbook?
Sid
Sid
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
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
>>>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
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
ASKER
Alright there you go Sid.. Dint know the T/C sorry :)
Sid.xlsx
Sid.xlsx
I just tried this and it works for me. Sample File Attached.
Sid
Sid.xls
Sub Sample()
On Error Resume Next
Sheets("1").Columns(1).EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub
Sid
Sid.xls
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.
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.
Ok. I have modified Eric's code. Please try this.
Sid
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
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).Entire Row.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
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
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).Entire
i=i+1 'add one to look at the row again to see if following was empty also..
End If
Next i
Application.ScreenUpdating
End Sub
Kr
Eric
Eric, No you don't need that extra line. The code that I gave in ID: 35068903 will do :)
Sid
Sid
Hi Sid,
Yes, you're right when comming from beneeth the row shifting up is already checked...
Kr
Eric
Yes, you're right when comming from beneeth the row shifting up is already checked...
Kr
Eric
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
But I would like to thanks Sid for your hardwork.
Glad to be of help :)
Sid
Sid
dim i , amnt as integer
amnt = ActiveSheet.UsedRange.Rows
for i=2 to amnt
if Cells(i,1) ="" then cells(i,1).entirerow.delet
next i
end sub
kr Eric