Forum Home
Press F1
Thread ID: 35901	2003-07-25 03:16:00	removing empty rows from Excel spreadsheet	keesspr (4297)	Press F1

Post ID	Timestamp	Content	User
162694	2003-07-25 03:16:00	I am using Ulead Photo Explorer to add descriptions to my photographs. I want to migrate the descriptions (text) to an MS Access database, together with the filename they belong to. The descriptive info is stored in Photo Explorer in a file with suffix .bas, created by Ulead. I tried to open it in Acces, but it doesn't let me. Neither does Excel. However, I can open it in MS Word. It shows my file names, followed each by 248 'spacers' (The letter y with an Umlaut above it ), a question mark followed by my descriptive text. By replacing (Find-Replace) I can add some commas as delimiters, save the file as plain text, close it, and re-open it in Excel. I end up with all my filenames in the A column, but separated by three empty rows each time. My descriptive text in the B column, be it three rows lower, again separated by three empty rows. I delete the first three cells from the B column, which moves my text into the same row as the corresponding filename in A. Almost there! Now, if only I can find a macro or other kind of utility that automatically removes all the three row gaps between my filename/text lines, I would end up with two tidy columns that I can copy and paste into a table of my Access database. It seems simple, but I'm stuck. Is there anyone out there who can tell me how to delete all these three row empty blocks. I could do it by hand, but I'm talking about an archive of 10,000 pix, and that would include a hell of a lot of work.	keesspr (4297)
162695	2003-07-25 03:31:00	When you open the file in Excel, you can tick the box to "treat consecutive delimiters as one" To get to this stage, do not have the file named as one recognised by Excel. From within Excel, File - Open - select All Files under Files of Type - browse to where your comma separated value file is and double click it (or select it and Open) This relies on Excel not recognising the filetype of course, otherwise it will auto-open. This should completely eliminate the need for your complex way around the issue, even perhaps the Word conversion?	godfather (25)
162696	2003-07-25 04:05:00	Yes, when I try and open the .bas file in Excel, I get the "This file is not in recognisable file format", message,and then press OK. If I open it like that, all the content of the .bas file ends up in one excel cell, with the text joined to the filename The original, as seen in Word, doesn't have any comma delimiters, that is why I add them in Word. I think the three empty lines are added because of those 248 'filler' characters (They originally showed as little squares, but now as the y with an accent) I've copied the first tow paragraphs of the bas.file, opened in Word, and will try to paste them here to show what they look like. The whole folder is 278 paragraphs, so I'll save you that! DSC02520.JPG 0Cutting vegetable DSC02511.JPG ?Roasting shallots and lemongrass Here, the filling characters don't show up however	keesspr (4297)
162697	2003-07-25 04:10:00	If they are truely blank rows, the following Excel macro will delete all of them, working from column A Sub REMOVE_BLANKS() Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub If they are not blanks but hard spaces, you can sort the data to remove the hard spaces. To be on the safe side, fill column C downwards with sequential numbers to the last row containing data. Put the cursor on the top left hand cell (A1) and Data/Sort Column A "Descending". All the hard space cells should be at the bottom of the data list. Delete all the cells containing the hard spaces as well as the corresponding numbers in column C. Again select cell A1 and Data/Sort but select column C and "Ascending" as the Sort option. Then delete column C, and the data will be in the same order as originally, but with the hard spaces removed. HTH	Russell D (18)
162698	2003-07-25 04:35:00	Thank You!! They were hard spaces after all, I just did a sort, and got exactly the result I wanted. So simple, just needed to use my brain! I spent several hours doing all kind of different things and got nowhere. Now I can proceed thanks again.	keesspr (4297)
1