Using VBA, how can I select every other cell in a row range (to be copied and pasted vertically)?

I have a 2200+ page text 开发者_如何学Pythonfile. It is delivered from a customer through a data exchange to us with asterisks to separate values and tildes (~) to denote the end of a row. The file is sent to me as a text file in Word. Most rows are split in two (1 row covers a full line and part of a second line). I transfer segments (10 page chunks) of it at a time into Excel where, unfortunately, any zeroes that occur at the end of a row get discarded in the "text to columns" procedure. So, I eyeball every "long" row to insure that zeroes were not lost and manually re-enter any that were.

Here is a small bit of sample data:

SDQ EA 92 1551 378 1601 151 1603 157 1604 83

The "SDQ, EA, and 92" are irrelevant (artifacts of data transmission). I want to use Excel and/or VBA to select 1551, 1601, 1603, and 1604 (these are store numbers) so that I can copy those values, and transpose paste them vertically. I will then go back and copy 378, 151, 157, and 83 (sales values) so that I can transpose paste them next to the store numbers. The next two rows of data contain the same store numbers but give the corresponding dollar values. I will only need to copy the dollar values so they can be transpose pasted vertically next to unit values (e.g. 378, 151, 157, and 83).

Just being able to put my cursor on the first cell of interest in the row and run a macro to copy every other cell would speed up my work tremendously. I have tried using ActiveCell and Offset references to select a range to copy, but have not been successful. Does any have any suggestions for me? Thanks in advance for the help.

It's hard to give a complete answer without more information about the file.

I think if your input data is 2200+ pages long, it's unlikely that opening it with the default excel opening functions is the way to go. Especially since Excel has maximum number of rows and columns. If the file is a text file (.txt) I would suggest opening it with VBA and reading each line, one at a time, and processing the data.

Here's an example to get you started. Just keep in mind that this is transposing each row of text into columns of data, so you will quickly fill all the columns of excel long before you run thru 2200 pages of text. But it's just an example.

Sub getData()
   dFile = FreeFile
   sFile = "c:\code\test.txt"

   Open sFile For Input As #dFile
    c = 1
    'keep doing this until end of file
    Do While Not EOF(dFile)
      'read line into dataLine
      Input #dFile, dataLine
      ' break up line into words based on spaces
      j = Split(dataLine, " ")
      jLength = UBound(j)
      If jLength > 2 Then
        r = 1
        'ignore first 3 words
        'and get every other word
        'transpose rows of text into columns
        For word = 3 To jLength Step 2
            Cells(r, c) = j(word)
            r = r + 1
        Next word
      End If
      c = c + 1
    Loop
  Close #Data
End Sub