I'm sure Tom appreciates the comments (and I do, too).



wrote:

Dave,
That worked beautifully! Oh my gosh. I have been trying to get that
to happen for 4 days. I cannot thank you (and Tom) enough for your
help! How cool is that!!!!
Kim

Dave Peterson wrote:
You're right. I think Tom had another(!) typo in his code.

This seemed to work ok.

Option Explicit
Sub ProcData2()
Dim rw As Long, rng As Range, i As Long
Dim sh2 As Worksheet, ar As Range
rw = 1
With Worksheets("Sheet1")
Set rng = .Columns(1).SpecialCells(xlCellTypeConstants)
End With
Set sh2 = Worksheets("Sheet2")
For Each ar In rng.Areas
For i = 1 To ar.Cells.Count
sh2.Cells(rw, i).Value = ar(i)
Next i
rw = rw + 1
Next ar
End Sub


But it does expect all the data to be values--not formulas.

And you don't have to change the names in excel. You could have changed the
names in the code.

With Worksheets("Sheet1")
could be:
With Worksheets("what ever your sheet name is here")

And this line
Set sh2 = Worksheets("Sheet2")
would change to
Set sh2 = Worksheets("your other sheet name here")

As long as they match what you see in the worksheet tab, you'll be ok.

wrote:

Dave,
The row that it is pointing out is the blank lines between the records.
I am going through the code so I can understand it better. Can you
let me know if I am understanding this correctly:

Sub ProcData()
Dim rw As Long, rng As Range
Dim sh2 As Worksheet, ar As Range
With Worksheets("Sheet1")
Set rng = .Columns(1).SpecialCells(xlBlanks)

Dim has set up storage space and allocated variables. It has set the
variable rng as the Range. The code then says in worksheet 1, assign
the blank lines in column 1 as the contents of that variable.

Is that correct? I'm hoping to understand it so I can work with it
accomplish my assignment.
Thanks!!!!!!!

Dave Peterson wrote:
Tom knew what you wanted to do when your groups had 3 or 4 rows, but he didn't
know what to do when a group had 1-2 or more than 4 rows.

So he shows you the offending row followed by how many rows in that group.

So row 4 only has a single row as well as row 8.



wrote:

Hey Dave,
That did fix my runtime error. Now I get a message that reads "4 has a
count of 1"
I click OK and then it says "8 has a count of 1" It keeps doing that
through the record and waits for my response.
Kim

Dave Peterson wrote:
try ar.count

wrote:

Thanks Tom,
You want a job :). I'm only half joking if you do contracting work let
me know. I haven't written code for 20 years but I'm the closest thing
my boss has. Poor thing.

I tried to run this and got an error on Sheet1 so I renaned the
worksheet sheet1 and it got past that. Then it stopped on worksheet2
so I created that and it got past that. Now I am getting a Run time
error '438' Object doesn't support thie property or method and it is
pointing to this line of code: ElseIf ar.cnt = 3 Then

Tom Ogilvy wrote:
Sub ProcData()
dim rw as Long, rng as Range
Dim sh2 as Worksheet, ar as Range
rw = 1
With worksheets("Sheet1")
set rng = .columns(1).Specialcells(xlblanks)
End with
set sh2 = Worksheets("Sheet2")
for each ar in rng.Areas
if ar.count = 4 then
for i = 1 to 4
sh2.cells(rw,i).Value = ar(i)
next
elseif ar.cnt = 3 then
for i = 1 to 3
sh2.cells(rw,i+1).value = ar(i)
next
rw = rw + 1
else
msgbox ar(1).Row & " has a cnt of " & ar.rows.count
end if
Next ar
End sub

--
Regards,
Tom Ogilvy




" wrote:

Hey fellow WVer! What part are you from? I do have a blank. This is
what I have right now:

YOUNG AMELIA
HC 33 BOX 2093
DORCAS WV 26847

YOUNG KENNETH K & LILA D
RONALD YOUNG
19 MEADOW RIDGE
PETERSBURG WV 26847

YOUNG RONALD LEE & SANDRA SUE
19 MEADOW RIDGE
PETERSBURG WV 26847

YOWLER ROBERT C & KAREN A
HC 75 BOX 109
NEW CREEK WV 26743

ZECK ELI & JOHN EDWARD
ANNABELLE ZECK
1212 FLEMING AVE
FAIRMONT WV 26554

ZELLMAN WILLIAM H JR & RUTH A
2909 PAPERMILL RD
PHOENIX MD 21131

ZETAH VIRGINIA BERGESTON
PO BOX 296
MAYSVILLE WV 26833


a7n9 wrote:
Do you have a blank row between each record? If that is the case, it
would be easy to put each row of one record in to different columns.

BTW. I'm from WV too.


--
a7n9


------------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149
View this thread: http://www.excelforum.com/showthread...hreadid=562556



--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson