Delete blank rows and export results

[junada0]

Hi,

I would like your help to create a process where I can delete blank rows on a range of data and then move the result to another range, and finally export the result as a .csv file to a directory.

I have added a sample file, which I would later expand to larger amounts of data.

Further instructions are enclosed within the "export" sheet of the attachemnt.

Appreciate any help.

Junada0

[jaslake]

Hi junada0

What do you want exported? Only the data in range H4:M13 of your example?
Why do you want to

move the result to another range

Is there a reason to do so?

John

[JBeaucaire]

This macro will copy the cells to H4:

Please Login or Register  to view this content.

This macro will move it to a CSV file:

Please Login or Register  to view this content.

[JBeaucaire]

This macro will do it all in on step without needing two buttons/macros:

Please Login or Register  to view this content.

[junada0]

Hi John,

The data in the example is just for demonstration purposes as my real data could be as much as 100 rows and approx 8 columns wide, with lots of empty rows which would then be condensed to say 60 or 70 rows ( without gaps).

In the example the only data that needs exporting is H4:M13 (but I would need the option to change any code to a larger range of say H4:P100)

The exported file would then be used for an import .csv file into MS project.

Junada0

[JBeaucaire]

If we just want to see how many rows/columns of data there are, that's doable. Make sure there are no extraneous rows or notes and try this:

Please Login or Register  to view this content.

[junada0]

You guys are the best, brilliant advice.

Could I ask just one more favour, where the code is:-
ActiveWorkbook.SaveAs Filename:="D:\Users\Brian\Documents\Export1.csv", FileFormat:=xlCSV, CreateBackup:=False
Instead of picking up the filename in the code, could this be edited to pick up the file name from a cell in the same sheet say in cell A1. This would prevent any users having to edit the code.

Sorry I am no expert in VBA, and I am on a big learning curve

Really appreciate any help.

[JBeaucaire]

Like this perhaps:

Please Login or Register  to view this content.

[junada0]

Hi,

Sorry to be picky, but I do prefer the enclosed method that you suggested (see enclosed), but would like to have option to pick up a file name from A1.

Would appreciate if you could take a look at this. I prefer this method as I can view the export before pressing the "export" button

I did try the last method but had constant error "Runtime error 1004"

Hope you can help

[JBeaucaire]

I'm not certain what you're expecting. Your sample sheet has a blank cell in A1.

[junada0]

The main change i need is to allow the file name and saved location to be added in A1, rather than editing the code.

I would like to type in a file name and location in A1 (D:\Users\Garry\Excel\export1.csv) and have the "export" button create the file "export1.csv"and save in the folder" D:\Users\Garry\Excel"


Hope this helps

[JBeaucaire]

The macro in post #8 does that. What am I missing?

[junada0]

I have got this far with version 3.1, but only column A get scopied, I need to copy range H4:M13.

Could you edit the code for the "export" macro

Many thanks

[JBeaucaire]

The macro is already working correctly. The problem is all the "notes" you've left in your sheet. Delete all those notes and leave only the actual data in columns A:F

Every time I run the macro on a clean sheet, it does what you want...creates a CSV from the data only skipping blanks and names it using the text string in cell A1.

[junada0]

OK, Now I get it.

Many thanks, now I get it.

Really appreciate all you help.

Case closed

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.