Delete Unused (4000) Rows
 

Help,
I have a Large workbook running multiple Macros to import, compare
and generate reports.
The Problem is that after all is said and done I endup with a workseet
that has about 5000 rows, when only maybe 1000 have data in them. The
other 4000 are blank, I nned to remove these as they increase the size
of the workbook exponentially. Since this worksheet has between 7 and
10 worksheets with the same problem.

Since I am deleting alout of rows I am also worried about the Macro
slowing down too much.
I already have Application.ScreenUpdating = False


Any Ideas or suggestions.

Delete Unused (4000) Rows
 

use something like:

With Sheets("sheet2")
.UsedRange
End With

If you've got a few sheets with consecutive numbers, you could try:

For i = 1 To Sheets.Count
With Sheets("sheet" & i)
.UsedRange
End With
Next


Regards

Trevor


"VexedFist" wrote in message
oups.com...
Help,
I have a Large workbook running multiple Macros to import, compare
and generate reports.
The Problem is that after all is said and done I endup with a workseet
that has about 5000 rows, when only maybe 1000 have data in them. The
other 4000 are blank, I nned to remove these as they increase the size
of the workbook exponentially. Since this worksheet has between 7 and
10 worksheets with the same problem.

Since I am deleting alout of rows I am also worried about the Macro
slowing down too much.
I already have Application.ScreenUpdating = False


Any Ideas or suggestions.

Delete Unused (4000) Rows
 

assume the last non empty cell in column A will determine where to start
deleting:

for each sh in ActiveWorkbook.worksheets
lastrow = sh.cells(sh.rows.count,1).End(xlup)(2).row
rows(lastrow & ":" & sh.rows.count).EntireRow.Delete
Next
ActiveWorkbook.Save

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


"VexedFist" wrote:

Help,
I have a Large workbook running multiple Macros to import, compare
and generate reports.
The Problem is that after all is said and done I endup with a workseet
that has about 5000 rows, when only maybe 1000 have data in them. The
other 4000 are blank, I nned to remove these as they increase the size
of the workbook exponentially. Since this worksheet has between 7 and
10 worksheets with the same problem.

Since I am deleting alout of rows I am also worried about the Macro
slowing down too much.
I already have Application.ScreenUpdating = False


Any Ideas or suggestions.

Delete Unused (4000) Rows
 

Tom,

This seems to delete eveything except 1 row from my worksheet?


Tom Ogilvy wrote:
assume the last non empty cell in column A will determine where to start
deleting:

for each sh in ActiveWorkbook.worksheets
lastrow = sh.cells(sh.rows.count,1).End(xlup)(2).row
rows(lastrow & ":" & sh.rows.count).EntireRow.Delete
Next
ActiveWorkbook.Save

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


"VexedFist" wrote:

Help,
I have a Large workbook running multiple Macros to import, compare
and generate reports.
The Problem is that after all is said and done I endup with a workseet
that has about 5000 rows, when only maybe 1000 have data in them. The
other 4000 are blank, I nned to remove these as they increase the size
of the workbook exponentially. Since this worksheet has between 7 and
10 worksheets with the same problem.

Since I am deleting alout of rows I am also worried about the Macro
slowing down too much.
I already have Application.ScreenUpdating = False


Any Ideas or suggestions.