importing file into Excel

[luv2bike2]

I will be exporting files from Crystal Reports and importing them into Excel.
My question is: is there a way i can remove all the Blank rows and Blank
columns in Excel in one shot or do i have to delete each line or group of
lines? If there is a way to do it in one shot how would i go about doing
that? I was thinking of doing a macro but the only problem i see is how
would i set up the marco to see if they is any data in any of the rows or
columns?
Any assistance would be greatly appreciated!

 

[luv2bike2]

i mentioned that i will be exporting files from Crystal Reports ---- i will
be exporting A LOT of reports (up to 150 or so) and when i have imported one
of the reports i had a lot of clean up to do (remove the rows and columns
that were blank) and it took over 20 minutes to do that... if there is a
quicker way than going to the row and deleting it i would appreciate it.

 

[JP]

Here's a sub that will delete blank rows. Place it in a standard
module. For implementation assistance, check out http://www.rondebruin.nl/code.htm

Sub Del_Empty_Rows()

Dim rng As Excel.Range
Dim A As Long
Application.ScreenUpdating = False

If Selection.Rows.count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If

With WorksheetFunction
For A = rng.Rows.count To 1 Step -1
If .CountA(rng.Rows(A).EntireRow) = 0 Then
rng.Rows(A).EntireRow.Delete
Next A
End With

Set rng = Nothing
Application.ScreenUpdating = True

End Sub

ps- You should posted code-related questions to microsoft.­public.­
excel.­programming.


HTH,
JP

 

[Ron Coderre]

In my experience, for that kind of volume, the most efficient way
to handle your situation is to contact the dept/individual responsible
for maintaining the Crystal Reports and ask to have one designed that
facilitates clean import into Excel.

Alternatively, your IT Dept might be able to schedule data dumps
of the information you need...either to a database table that you can query
via MS Query/Excel or in a format that Excel can import.

I hope that helps.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[luv2bike2]

Thank you JP.
I will give this a shot.
I will get back to you as to how it went!

 

[luv2bike2]

Thank you Ron.
Unfortunately, i am IT person and the Crystal Report person. I know very
little about Crystal Reports and have no experience with Data Dumps.

i do appreciate your input though.

 

[JP]

Yikes, sorry to hear that!

--JP

 

[luv2bike2]

JP,

I followed the directions from that webpage that you suggested to go to to
insert the Sub you forward on.
When i went to run it, an error message came up:

"Next without For"

and it was pointing to the line Next A and below that it was End With.

can you please help me out here as to what i need to put in so i won't get
the error message?

Thanks so much!

 

[JP]

Looks like a word-wrapping error. This should be all one line:

If .CountA(rng.Rows(A).EntireRow) = 0 Then
rng.Rows(A).EntireRow.Delete

Just go to the end of "If .CountA(rng.Rows(A).EntireRow) = 0 Then"

and press Delete until the If-Then statement is one line.


HTH,
JP

 

[luv2bike2]

Thank You agan JP.
That worked and it will save me and another person a lot of time!!!

Greatly appreciated

 

[JP]

Glad to hear it!

--JP