extra empty rows in Excel file

[Linda]

My Excel file has 950 rows of data. Somehow, I now have
over 58,000 rows of empty cells; the file size has grown
from 500kb to 2100 kb. How do I delete the empty rows?

 

[Berend Botje]

Using vba you can try something like:

Sub DeleteRows

Dim Row as integer
Dim Col as integer
Dim CountingValue as integer

For row = 1 to 60000
Col = 1
For col = 1 to 10
if len(cells(row,col).value) > 0 then
CountingValue = CountingValue+1
goto nextRow
end if
Next Col
NextRow:
if Countingvalue >0 then
Cells(row,1).entirerow.delete
Countingvalue = 0
end if
Next Row

end sub


The script checks the first 10 cells of each row. If all of them ar
empty, the row is presumed empty and is deleted. If one or more of the
contain any data, the row is not deleted

 

[Andy B]

Hi

Have a look here:
http://www.contextures.com/xlfaqApp.html#Unused

 

[Linda]

-----Original Message-----
Using vba you can try something like:

Sub DeleteRows

Dim Row as integer
Dim Col as integer
Dim CountingValue as integer

For row = 1 to 60000
Col = 1
For col = 1 to 10
if len(cells(row,col).value) > 0 then
CountingValue = CountingValue+1
goto nextRow
end if
Next Col
NextRow:
if Countingvalue >0 then
Cells(row,1).entirerow.delete
Countingvalue = 0
end if
Next Row

end sub


The script checks the first 10 cells of each row. If all of them are
empty, the row is presumed empty and is deleted. If one or more of them
contain any data, the row is not deleted.

THanks; I'm not familiar with using VBA. Is there another
method?

 

[Gord Dibben]

Linda

Select a sheet.

Hit CRTL + END keys.

Where does this take you?

If far below and to right of your real data range, select all rows below your
data and Edit>Delete.

Do the same for columns to the right.

Follow these steps for each sheet.

NOW IMPORTANT STEP.....Save your workbook. Close and re-open. Your used
range should now be much smaller.

Gord Dibben Excel MVP