Find First Empty Row and Delete

[Yellowbird]

I have been able to cobble together a working macro via assistance
from folks in this group and I thank everyone for their insight! I am
a newbie to macros and am quite happy to have everything (almost)
working as I export my data from Crystal Reports to Excel, cleanup
some issues, and then export the data to a CSV file.

When my data comes into Excel there are a variable number of rows with
data. For example, today I imported the data and there were 1425 rows
with data. I run my "cleanup" macro to format everything properly and
it all looks good. Except...I have four rows (1426-1429) at the end of
the worksheet that have values in some but not all columns (due to a
problem within my macro I think). I can either fix my formatting macro
(although I have been messing with it for quite awhile now) or I can
search column A1 for the first blank cell (since the extraneous rows
with data in certain columns have a blank A1 cell), select those rows,
and then delete them.

So...any suggestions on a way to select the first blank cell in column
A1 (remember, the length of the worksheet is variable), then highlight
that row and any beneath it and delete them?

I've also posted my macro below in case anyone has some free time to
analyze and point out where the problem lies that is actually causing
the extra lines. The extra four rows only end up with values in
columns Y, Z, and AC.

Again, because I'm new to macros, there are probably ways to
streamline things, but I'm still learning.

As always, thanks for any guidance and assistance,
Yellowbird

Existing macro:

Sub Cleanup()

' Delete the first four blank rows in the worksheet (these are created
on import from Crystal Reports)

Range("A1:A4").Select
Selection.EntireRow.Delete

' Remove the extra zeroes preceding the Line Number values

Columns("W:W").Select
Range("W2, W65536").End(xlUp).Offset(1, 0).Activate
Selection.Find(What:="000", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="000", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

' Replace the full Units of Measure with abbreviations

Columns("Z:Z").Select
Range("Z2, Z65536").End(xlUp).Offset(1, 0).Activate
Selection.Replace What:="EACH", Replacement:="EA", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="", Replacement:="EA", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True

Columns("Z:Z").Select
Range("Z2, Z65536").End(xlUp).Offset(1, 0).Activate
Selection.Replace What:="CASE", Replacement:="EA", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=True

Columns("Z:Z").Select
Range("Z2, Z65536").End(xlUp).Offset(1, 0).Activate
Selection.Replace What:="DZN", Replacement:="EA", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=True

Columns("Z:Z").Select
Range("Z2, Z65536").End(xlUp).Offset(1, 0).Activate
Selection.Replace What:="PAIR", Replacement:="PR", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=True

' Delete apostrophes from PF Changs in Item Description field

Columns("AB:AB").Select
Range("AB2, AB65536").End(xlUp).Offset(1, 0).Activate
Selection.Replace What:="CHANG'S", Replacement:="CHANGS",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True

' Delete any commas that appear in Item or Product Description field

Columns("AB:AB").Select
Range("AB2, AB65536").End(xlUp).Offset(1, 0).Activate
Selection.Replace What:=", ", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

' Replace any double quotes in Item or Product Description field

Columns("AB:AB").Select
Range("AB2, AB65536").End(xlUp).Offset(1, 0).Activate
Selection.Replace What:="""", Replacement:=" IN.", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=True

' Replace any single quote in Item or Product Description field

Columns("AB:AB").Select
Range("AB2, AB65536").End(xlUp).Offset(1, 0).Activate
Selection.Replace What:="'", Replacement:=" FOOT", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=True

' Correctly format currency values and zero values in currency fields

Columns("G:G").Select
Range("G2, G65536").End(xlUp).Offset(1, 0).Activate
Selection.NumberFormat = "0.00;-0.00;0"

Columns("H:H").Select
Range("H2, H65536").End(xlUp).Offset(1, 0).Activate
Selection.NumberFormat = "0.00;-0.00;0"

Columns("I:I").Select
Range("I2, I65536").End(xlUp).Offset(1, 0).Activate
Selection.NumberFormat = "0.00;-0.00;0"

' Replace any empty cells with 0

Columns("Y:Y").Select
Range("Y2, Y65536").End(xlUp).Offset(1, 0).Activate
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

' Correctly format Line Number field for four characters

Columns("W:W").Select
Range("W2, W65536").End(xlUp).Offset(1, 0).Activate
Selection.NumberFormat = "0000;0"

' Replace any empty cells with 0

Columns("AC:AC").Select
Range("AC2, AC65536").End(xlUp).Offset(1, 0).Activate
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

' Correctly format currency values and zero values in currency fields

Columns("AC:AC").Select
Range("AC2, AC65536").End(xlUp).Offset(1, 0).Activate
Selection.NumberFormat = "0.00;-0.00;0"

' Correctly format entire worksheet for viewing purposes

Columns("A:AJ").Select
Range("A1, AJ65536").End(xlUp).Offset(1, 0).Activate
With Selection.Font
.Name = "Times New Roman"
.Size = 2
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.ColorIndex = 1
End With
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With

' Return highlight to first cell in worksheet to indicate that cleanup
is complete

Range("A1").Select

End Sub

 

[Frank Kabel]

Hi
without looking at your macro you may use the following macro which
clears all rows which are empty in column A

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.usedrange.rows.count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

 

[Ron de Bruin]

Or without a loop if you only check one column

This one can be a problem
http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

Sub DeleteBlankRows()
On Error Resume Next 'In case there are no blank rows
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

 

[Ron de Bruin]

If I understand you correct

Try this

Dim FirstEmpty As Long
FirstEmpty = Columns("A").SpecialCells(xlCellTypeBlanks).Cells(1).Row
Rows(FirstEmpty & ":" & Rows.Count).Delete

 

[Yellowbird]

Thanks, Frank and Ron.

These are useful and work if I pare down my macro, but don't work with
the formatting I have in place for columns Y, Z, and AC. All other
columns in the "extra" rows are blank, but these have formatting in
the four extra rows. So I actually think my problem lies with the
manner in which I am selecting and formatting the values in those
columns. Maybe it would be better if I dealt with that so that I don't
end up with the blank rows at the end of the worksheet and this "find
and delete extra rows" is not part of the total picture.

Hopefully I am making sense and someone can point me in the right
direction after viewing my complete macro.

Thank you again to all who offer suggestions.

Regards,
Yellowbird