Find last row of data and delete empty rows

[Pat]

I need a macro that will find the last row in colA containing data and then
delete the next 100 empty rows.
Thank you if you can help.
Pat

 

[Chip]

Try this
Sub deletenext100()
lastrow = ActiveSheet.UsedRange.Rows.Count
Range(Cells(lastrow + 1, 1), Cells(lastrow + 100, 1)).Select
Selection.EntireRow.Delete
End Sub

 

[Tom Ogilvy]

cells(rows.count,1).End(xlup) _
.offset(1,0).Resize(100).Entirerow.Delete

 

[Pat]

Hi gentlemen,

Thank you for your help. Both suggestions has not been successful. Perhaps
it needs to be modified to reflect its use. The empty rows the code is
deleting is below where the copy/paste is being performed. The empty rows
are in fact within the copy/paste region and it is these rows which need to
be deleted.


Application.DisplayAlerts = False
Range("D8:J930").Select
Selection.Copy

ChDir _
"C:\Documents and Settings\All Users\Documents\My Documents\Excel
books 2005"
Workbooks.Open Filename:= _
"C:\Documents and Settings\All Users\Documents\My Documents\Excel
books 2005\PrepareForHLS.xls"
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False

' delete next 100 rows
LastRow = ActiveSheet.UsedRange.Rows.Count
Range(Cells(LastRow + 1, 1), Cells(LastRow + 100, 1)).Select
Selection.EntireRow.Delete

ChDir "C:\HLSexcel"
ActiveWorkbook.SaveAs Filename:="C:\HLSexcel\ReadyForHLS.csv",
FileFormat:= _
xlCSV, CreateBackup:=False
Application.DisplayAlerts = True
Windows("Management.xls").Activate

Range("D10").Select


Pat