Need Help cleaning up Macro that imports text file and deletes unnecessary rows

So I am completely new to VBA (like just started yesterday and have no programming experience) and was trying to create a macro that took a text file and converts it into an excel worksheet. With the aid of the internet, I was able to piece together the following code get it to work after spending an afternoon looking things over. However, I feel like there might be a more efficient way of handling the row deletion as the worksheet hangs for a bit and eventually gives me the "Error Running Macro" message. Does anyone have any suggestions or see anything that is unnecessary? Appreciate the help!

Please Login or Register  to view this content.

Hello ppilot,

The problem is when the For Next loop completes, the ErrHandler code is next in line to execute. Since there are no conditions to control its execution, the message is always displayed.

I have changed the handler to check if there is an error. If an error is detected, the error number and description are included in the message.

Please Login or Register  to view this content.

Much appreciated! I am still getting a handle on being able to interpret the flow of VBA code, but that makes sense. As for the row deletion process, is there a more efficient way of handling it is the lf not loop good enough?

Hello ppilot,

It really depends on how your data is arranged. One method which is faster is to create a combined range of all the rows to be deleted and then delete the rows all at once.

Another method involves using an array. The contents of the range are read into the array if they are not to be deleted. The original range is cleared and the array is copied back. This method is very quick provided you don't need to copy cell formatting.

I copy and pasted two line items from the text file as an example of the data arrangement:

(Date) (Text and Number) (Number1) (Number2) (Number3) (Number4)
(Number5)

01Mar13 XXX IV-999999 123456-859 2,517.64 2,517.64 122
159866
16Apr13 XXX IV-999998 123457-978 1,798.19 1,798.19 76
161654

The second line is deleted along with the page headers so I end up with the first line broken out into six different columns. Sometimes the text file can be up to 20 pages long. Does that help?

For some reason the message board is pushing (Number5) over to the very left when it should be indented slightly under the (text and number).

Hello ppilot,

In this case, using the array would be the best. It will eliminate the need to make a second pass and delete rows. I am working on the code for you.

Hello ppilot,

Almost done. I have a question about the dates. do they appear like 01Mar13 or like 01-Mar-13?

Hello ppilot,

That was my assumption. Just wanted to be sure. Thanks.

Hello ppilot,

Here is the macro using an array to create a contiguous block of data. It is a bit long mostly due to all the comments. I thought you may want to understand what the code is doing.

Please Login or Register  to view this content.