Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last row of data and delete empty rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last row of data and delete empty rows
Try this
Sub deletenext100() lastrow = ActiveSheet.UsedRange.Rows.Count Range(Cells(lastrow + 1, 1), Cells(lastrow + 100, 1)).Select Selection.EntireRow.Delete End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last row of data and delete empty rows
cells(rows.count,1).End(xlup) _
.offset(1,0).Resize(100).Entirerow.Delete -- Regards, Tom Ogilvy "Pat" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
delete rows with empty cells throughout the data | Excel Worksheet Functions | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
how do i find and delete all empty rows in an excel worksheet | Excel Worksheet Functions |