Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i delete unused row and columns ??? | Excel Discussion (Misc queries) | |||
I have 4000+ rows in a worksheet, how to delete 100's at a time? | Excel Worksheet Functions | |||
How can i randomly select 780 rows from 4000 rows of data | Excel Worksheet Functions | |||
VB Code to Delete Unused Rows | Excel Programming | |||
How can I insert 4000 rows of Excel data into a sql database | Excel Programming |