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

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

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



"Pele" wrote in message
...
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


---
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

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 :-)
----------------------------------------------------------
------------------



"Pele" wrote in
message
...
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


---
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


.

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
-----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 :-)
----------------------------------------------------------
------------------



"Pele" wrote in
message
...
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


---
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


.

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.Del ete
.AutoFilter
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 :-)
----------------------------------------------------------------------------



"Pele" wrote in message
...
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
-----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 :-)
----------------------------------------------------------
------------------



"Pele" wrote in
message
...
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


---
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


.



---
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: 02/11/2004

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


-----Original Message-----
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.Del ete
.AutoFilter
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 :-)
----------------------------------------------------------
------------------



"Pele" wrote in
message
...
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
-----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 :-)
--------------------------------------------------------
--
------------------



"Pele" wrote in
message
.. .
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


---
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


.



---
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:
02/11/2004


.

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.

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

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



"Pele" wrote in message
...
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


-----Original Message-----
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.Del ete
.AutoFilter
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 :-)
----------------------------------------------------------
------------------



"Pele" wrote in
message
...
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
-----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 :-)
--------------------------------------------------------
--
------------------



"Pele" wrote in
message
. ..
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


---
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


.



---
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:
02/11/2004


.



---
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