Find last row of data and delete empty rows
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
"Chip" wrote in message
oups.com...
Try this
Sub deletenext100()
lastrow = ActiveSheet.UsedRange.Rows.Count
Range(Cells(lastrow + 1, 1), Cells(lastrow + 100, 1)).Select
Selection.EntireRow.Delete
End Sub
|