Hi there

I have tried various methods but still can't come up with the solution.I need to write a code which finds blank cells in Column A of an Excel worksheet. And when there is a blank cell in column A, the entire row will be deleted and the rows below it will shift up to occupy that row.The number of rows in column A is not fixed and it will vary from one worksheet to another worksheet.Pls help.Thanks

David

You could try this...(off the top of my head)


Sub DeleteBlankRows()

Range("A1").select
Do
If activecell = "" Then
activecell.entirerow.delete shift:=xlUp
activecell.offset(-1,0).select
End If
activecell.offset(1,0).select
Loop until....(insert some test for the end of the file).

End Sub

Hi SR22Mike

The problem I had was to determine the last row in column A as this changes from one worksheet to another.So I am not sure how to to do the loop command.

Sub DeleteRowsWithBlanks()
Dim lastrow As Long
lastrow = Range("A:A").SpecialCells(xlLastCell).Row
Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
End Sub

'This is a good example of the dreadful performance of VBA loops.
'To test, put both of the following into a new workbook, then run each.

Sub DeleteRowsWithBlanks()
Dim lastrow As Long
ActiveWorkbook.Sheets.Add
Range("A1").Value = "foo"
Range("A5000").Value = "bar"
Start = Now()
lastrow = Range("A:A").SpecialCells(xlLastCell).Row
Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
Cells(1, 2).Value = Now() - Start
Cells(1, 2).NumberFormat = "ss.sss ""seconds"""
End Sub


Sub DeleteBlankRows()
Dim lastrow As Long
Dim i As Long
ActiveWorkbook.Sheets.Add
Range("A1").Value = "foo"
Range("A5000").Value = "bar"
Start = Now()
lastrow = Range("A:A").SpecialCells(xlLastCell).Row
Range("A1").Select
i = 1
Do While i < lastrow
If ActiveCell = "" Then
ActiveCell.EntireRow.Delete shift:=xlUp
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
Cells(1, 2).Value = Now() - Start
Cells(1, 2).NumberFormat = "ss.sss ""seconds"""
End Sub

Hi actuary

I have a worksheet with about 30,000 rows and I tried this code :
Sub DeleteBlankRows2()
Dim lastrow As Long
Dim i As Long
lastrow = Range("A:A").SpecialCells(xlLastCell).Row
Range("A1").Select
i = 1
Do While i < lastrow
If ActiveCell = "" Then
ActiveCell.EntireRow.Delete shift:=xlUp
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
End Sub

It took quite a while to do the code. I tried the code that you have given meub DeleteRowsWithBlanks()
Dim lastrow As Long
lastrow = Range("A:A").SpecialCells(xlLastCell).Row
Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
End Sub

But the whole worksheet turn out blank. Is there a problem with it or is there a faster way other than the first code? Thanks

My code is just a VBA version of what can be done with Excel's native functionality. Try doing it manually on the original worksheet: Use your mouse to select all the cells in column A all the way to the bottom of your data. Then choose Edit, Go To..., Special..., Blanks. Then choose Edit, Delete..., Entire row.

If the above doesn't work correctly, then it's a problem with the worksheet, not the code. You'll need to zip it and post it here.

Here is a shrotened version that allows you to choose the column upon which to base the deletion.

Hi shades

Thanks for the code.It works perfectly.But if I want to fix the column as the first column(eg:Column A),what value do I set myColm as?Thanks

If you want to operate on the whole column anyway, just use:

'This seems to be just as fast as selecting a limited number of rows.

Hi there

I tried the code already but this time with 2 different files. One file that has 10 rows and the code works perfectly.Another worksheet with 30,000 rows but the result with the one with the 30,000 rows is a blank worksheet. Is there a limitation or something like that? Thanks

I don't know, but if you'll post the spreadsheet we can find out.

Have you tried it manually like I asked? What happened?

Are you absolutely sure the macro produces a blank sheet on the 30,000 row version? In other words, did you press CTRL+Home to go to the top of the sheet and still you saw no rows?

The InputBox allows you to select the Column. So move the mouse over Column A heading and click.

Hi actuary

I tried the code on the 30,000 row worksheet and it turned out blank. My 30,000 row worksheet has data from columns A to Y.And it is filled also from row 1 to row 30,000. But in column A each alternate row is blank .

I give up. Post the spreadsheet or don't post at all.

Hi actuary

Sorry. I have uploaded the file. Pls take a look. Thanks