Delete blank rows in Excel with macro

  • Thread starter Thread starter Pele
  • Start date Start date
P

Pele

I had posted this problem earlier but I then realized that
I had not explained myself properly (hence my reposting
it).

I have a report downloaded into excel and I need to clean
the report to remove blank rows and embedded field names.
Below is a macro I tried to use, but it's not working. I
want to be able to check the contents of column A and
decide if it is blank or whether it contains the field
name called "Plnt". If the cell is blank or contains the
text "plnt", then the macro should delete the row and move
on to check the next row.

Please help if you know what to do.

Pele

Sub MacDeleterows()
'
' MacDeleterows Macro
'

Range("a2").Select
Do Until ActiveCell.SpecialCells(xlCellTypeLastCell)

If ActiveCell = "" Or ActiveCell = "plnt" Then
Selection.EntireRow.Delete
'ActiveCell.Offset(1, 0).Select
'Range("A1").Select
'ActiveCell.SpecialCells(xlLastCell).Select
End If
Loop
End Sub
 
Try this perhaps:-

Sub CleanUp()
With Columns("A:A")
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.AutoFilter Field:=1, Criteria1:="plnt"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub
 
Thanks..it worked
-----Original Message-----
Try this perhaps:-

Sub CleanUp()
With Columns("A:A")
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.AutoFilter Field:=1, Criteria1:="plnt"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

---------------------------------------------------------- ------------------
It's easier to beg forgiveness than ask permission :-)
---------------------------------------------------------- ------------------






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004


.
Click to expand...
 
Ken,

I just noticed that the code written happens to also
delete the initial row of field names in row 1 but I would
like to keep that row. What do I need to do to ensure that
the first row (field names) is kept. I am sorry I didn't
specify this earlier.

Pele
 
How about this?

Sub CleanUp()

On Error Resume Next

With ActiveSheet
LastRw = .Cells(Rows.Count, "A").End(xlUp).Row
Set Rng1 = .Range(Cells(1, "A"), Cells(LastRw, "A"))
Set Rng2 = .Range(Cells(2, "A"), Cells(LastRw, "A"))
End With

With Rng1
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.AutoFilter Field:=1, Criteria1:="plnt"
Rng2.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With

End Sub
 
Ken,

The code did leave the first line of record for the field
name, but I also noticed that it did not delete the very
last row (whose column A was Blank). That last row should
have been deleted and it was actually deleted in the last
edition of your code. Let me know what needs to change.

Pele
 
Is there a consistent column I can reliably use to determine the last row of
data? If none then that can be gotten round too.
 
Back
Top