PDA

View Full Version : Solved: Remove blank rows



tpoynton
12-05-2005, 09:22 AM
Greetings,

I have a spreadsheet that has about 300 rows of data in about 12 columns. the problem is that there are over 40,000 blank rows, which seems to be increasing the file size tremendously. When i hide the blank rows, the file size decreases from over 2mb to 1mb. Is there a way to delete the blank rows? i have tried highlighting and selecting "delete" from the right-click menu, but nothing gets rid of the extra blank rows.

I copied and pasted the data to a new file, and the file size is now 73kb. This is one way to solve the problem, but I guess I am just curious to know if there is a way to get rid of the blank rows without creating a new file.

Also, I dont know why the data sheet has all the extra rows. I do know this data is extracted from a much larger database...

THANKS!

austenr
12-05-2005, 09:29 AM
Hi Tpoynton,

I am not sure if you are saying you have a bunch of blank rows intersperced in your data or if they are somewhere at the end. Anyway, this should remove the blank lines if they are inside you data:

Sub DelRows()

Dim MyRow As Range
Dim MyCell As Range
Dim BlankRow As Boolean


For Each MyRow In ActiveSheet.UsedRange.Rows

BlankRow = True

If Intersect(MyRow, Range("A:A")).Value <> "" Then

For Each MyCell In Intersect(MyRow, Range("B:IV"))

If MyCell.Value <> "" Then BlankRow = False

Next MyCell

If BlankRow Then MyRow.EntireRow.Delete

End If

Next MyRow

End Sub

tpoynton
12-05-2005, 09:42 AM
THANK YOU! Turns out all the rows are in fact at the end (not interspersed in the data), and your sub worked brilliantly. THANKS AGAIN!

austenr
12-05-2005, 10:52 AM
No problem. Glad to help