I have an Excel file that has a listing of files from multiple directories that was created by importing a
DOS directory listing. it's format is:

Path

File1
File2
File3

for 50,000 files - all in column A.

Can someone write a macro to copy each path to column B in the appropriate row.

The keystrokes a

The keystrokes are (starting in A1):
edit copy 'Copy the path
right
down 2 'to the file info
Shift-left-end-down-right 'get the whole section
edit paste 'paste the path info
end down 'go to the bottom of the path info
down 2 'down 2 more
left 'left to get to the next path

where it will be repeated.

Thank you in advance

I'm lost. Apart from moving down across and every which way, what are you
doing? What is the objective, the format of the data?

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
...
I have an Excel file that has a listing of files from multiple directories
that was created by importing a
DOS directory listing. it's format is:

Path

File1
File2
File3

for 50,000 files - all in column A.

Can someone write a macro to copy each path to column B in the appropriate
row.

The keystrokes a

The keystrokes are (starting in A1):
edit copy 'Copy the path
right
down 2 'to the file info
Shift-left-end-down-right 'get the whole section
edit paste 'paste the path info
end down 'go to the bottom of the path info
down 2 'down 2 more
left 'left to get to the next path

where it will be repeated.

Thank you in advance

I'm trying to copy the path to the appropriate row so that I can look up the location of a file after I sort
the file names.

example before sort:

Path 1

File8 Path 1
File3 Path 1
File61 Path 1

Path2

File4 Path 2
File9 Path 2
File33 Path 2

after sort
File3 Path 1
File4 Path 2
File8 Path 1
File9 Path 2
File33 Path 2
File61 Path 1



On Wed, 8 Dec 2004 09:55:30 -0000, "Bob Phillips" wrote:

I'm lost. Apart from moving down across and every which way, what are you
doing? What is the objective, the format of the data?

OK, I understand better now. How do you know what is a path row and what is
a file row?

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
...
I'm trying to copy the path to the appropriate row so that I can look up
the location of a file after I sort
the file names.

example before sort:

Path 1

File8 Path 1
File3 Path 1
File61 Path 1

Path2

File4 Path 2
File9 Path 2
File33 Path 2

after sort
File3 Path 1
File4 Path 2
File8 Path 1
File9 Path 2
File33 Path 2
File61 Path 1



On Wed, 8 Dec 2004 09:55:30 -0000, "Bob Phillips"
wrote:

I'm lost. Apart from moving down across and every which way, what are you
doing? What is the objective, the format of the data?

The format is always:

Path
Blank row
files (from 0-many)
totals
blank row

Then this repeats for 275,000 rows
example follows

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\blitz

02/13/2002 04:04p <DIR .
02/13/2002 04:04p <DIR ..
10/01/1998 03:07p 73,322 BLITZ.ELM
11/16/1998 10:10p 302 BLITZ.INF
2 File(s) 73,624 bytes

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\blueprnt

02/13/2002 04:04p <DIR .
02/13/2002 04:04p <DIR ..
10/01/1998 03:07p 30,264 BLUEPRNT.ELM
11/16/1998 10:10p 317 BLUEPRNT.INF
2 File(s) 30,581 bytes

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\boldstri

02/13/2002 04:04p <DIR .
02/13/2002 04:04p <DIR ..
10/01/1998 03:07p 33,556 BOLDSTRI.ELM
11/16/1998 10:10p 343 BOLDSTRI.INF
2 File(s) 33,899 bytes




On Wed, 8 Dec 2004 12:31:43 -0000, "Bob Phillips" wrote:

OK, I understand better now. How do you know what is a path row and what is
a file row?

Here's a shot

Sub TidyUp()
Dim cLastrow As Long
Dim i As Long, j As Long
Dim fBlank As Boolean
Dim sDir As String

Range("A1").EntireRow.Insert
cLastrow = Cells(Rows.Count, "A").End(xlUp).Row
With Range(Cells(1, 1), Cells(cLastrow, 1))
.AutoFilter Field:=1, _
Criteria1:="=*<DIR*", _
Operator:=xlOr, _
Criteria2:="=*File(s)*"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Columns("A:A").AutoFilter

cLastrow = Cells(Rows.Count, "A").End(xlUp).Row
fBlank = True
For i = 1 To cLastrow
If fBlank Then
sDir = Right(Cells(i, "A").Value, Len(Cells(i, "A").Value) - 13)
i = i + 1 ' skip the blank
fBlank = False
Else
fBlank = Replace(Replace(Cells(i, "A").Value, " ", ""),
Chr(160), "") = ""
If Not fBlank Then
j = j + 1
Cells(j, "B").Value = Right(Cells(i, "A").Value, Len(Cells(i,
"A").Value) - _
InStrRev(Cells(i, "A").Value, " "))
& _
" " & sDir
End If
End If
Next i
Range("E18").Select

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
...
The format is always:

Path
Blank row
files (from 0-many)
totals
blank row

Then this repeats for 275,000 rows
example follows

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\blitz

02/13/2002 04:04p <DIR .
02/13/2002 04:04p <DIR ..
10/01/1998 03:07p 73,322 BLITZ.ELM
11/16/1998 10:10p 302 BLITZ.INF
2 File(s) 73,624 bytes

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\blueprnt

02/13/2002 04:04p <DIR .
02/13/2002 04:04p <DIR ..
10/01/1998 03:07p 30,264 BLUEPRNT.ELM
11/16/1998 10:10p 317 BLUEPRNT.INF
2 File(s) 30,581 bytes

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\boldstri

02/13/2002 04:04p <DIR .
02/13/2002 04:04p <DIR ..
10/01/1998 03:07p 33,556 BOLDSTRI.ELM
11/16/1998 10:10p 343 BOLDSTRI.INF
2 File(s) 33,899 bytes




On Wed, 8 Dec 2004 12:31:43 -0000, "Bob Phillips"
wrote:

OK, I understand better now. How do you know what is a path row and what
is
a file row?