Deleting blank rows in excel 3

[ulicki]

Hi,

I have exported data from Crystal Reports to Excel. During my export I am getting blank rows between each record (I have tried many things on the Crystal end to get this from happening). I want to delete these blank rows. My export is 12 columns wide if that matters.

Thanks for any help/suggestions.

 

[AngO]

Sort your records by any column. All the blank rows will be below the rest of the data.

If you don't have a column to sort by, and need to retain the original order, add a column. Number the new column
1

2

Selecte the 1 [blank row] 2 [blank row], then use the autofill handle to fill down. Then sort by the new column.

Best of luck,
AngO

 

[ulicki]

Thanks AngO,

It is a bit of a hassle as not every other row is blank. But I can put the numbering in my crystal report before exporting.

Thanks again

 

[AngO]

Glad to hear the numbering can occur before export, making it much easier to fix in Excel.
Good Luck,
AngO

 

[fpd833]

If you are familiar with macros, the following code will delete all empty rows in the used range. (The entire row must be blank)Easy, one click solution.

Sub DeleteEmptyRows()
'
'Deletes all empty rows in the used range
'
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub

 

[ulicki]

Thanks fpd833,

It worked.

 

[DaleWatson123321]

fpd833,

Thanks for your contribution, and I'll pass along a "STAR" for your example. I too am still learning VBA (as we all are - there are so many aspects to learn), so I appreciate your example.

Your example also worked fine for me during my initial testing. However, I just had to test out your example "further", to see what implications there are for a LARGER application.

I therefore entered data into the "lower reaches" of Excel's rows - around 65,000. And, guess what. I waited for 37 minutes and finally "gave up".

Now for my contribution... Because my background is heavily weighted on the Excel "functionality side", I've been exploring the "database functionality" fairly deeply.

I had previously written a VBA routine for another Tek-Tips posting, whereby the routine utilized this "database functionality". When I double-checked this example file, I found that I had populated the "database" down to row 65,535. The routine extracts rows containing data to the next sheet.

And guess how long it took ......exactly 2 seconds - Yes "2 seconds" (not the 37++ minutes).

My point is that, while "straight VBA" is quite useful in many situations, one should NOT overlook the "pure power" of the "built-in" functionality of Excel.

If anyone would like a copy of this Extraction Example, please feel free to ask. Just email me, and I'll return the file via return email.

A final note: The example provides an additional option to check "specific columns" for being blank.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ulicki]

Double Thanks to Dale.

The code really flew, I mean really flew. And it also included examples on how to delete rows based on cell contents which even helps more as I would like to keep some of the blank columns in the spreadsheet. Now I can place a value in a cell of the column I want to delete.