HELP WITH THIS MACRO

Eddie, which column contains data right down to the last row? Your macro is using column E to determine the last row of data. Change your line of code that reads:

For i = 1 To Range("E65536").End(xlUp).Row Step 2

to the appropriate column.

Regards,
BarrieBarrie Davidson

Barrie: All the columns have the same amount of data. I tried changing the line you mentioned but it still stops are row 739. Any other suggestions?

Eddie, I think I see your problem. Try:

Sub WEIGHT_PLACER_QUARTERLY()
iend = Range("E65536").End(xlUp).Row
For i = 1 To Range("E65536").End(xlUp).Row Step 2
Rows(i + 1).Insert
iend = iend + 1
Range("E" & i & ":G" & i).Cut Range("B" & i + 1)
Next
End Sub

Does this work for you?

Barrie
Barrie Davidson

Barrie: It still stops at row 739!!

Eddie

Hmmm..a mystery. Can you send me a copy of your spreadsheet (clearing out any sensitive data, of course)?

Barrie

Barrie: What is your email? Also if you look at wwwboard/messages/2596 you will find the same problem with another way of doing it, however, i dont know how to modify that code to make it work. i will send you the spread sheet

b_davidso@yahoo.ca

Also ....

You might also want to change your code to a faster method (as you requested in an earlier posting). Have a look at :-
2764.html

Re: Also ....

I tried that code but it didnt work, and I am not nearly savvy enough to modify it to work!!

Re: Also ....

Well since Barrie will have a copy of your workbook, he will be able to adjust the faster code for you to make it work.
(Since the slower code(with the loop) doesn't work either, it seems strange that you only asked about the slow code?)

Eddie, I am having problems with my Yahoo e-mail so I'm going this route. I had a look at your spreadsheet and came up with this code.

Sub WEIGHT_PLACER_QUARTERLY()
Application.ScreenUpdating = False
Application.StatusBar = "Processing........."
iend = Range("E65536").End(xlUp).Row
counter = 0
i = 1
Do Until counter = iend
Rows(i + 1).Insert
Range("E" & i & ":G" & i).Cut Range("B" & i + 1)
i = i + 2
counter = counter + 1
Loop
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

Try it and let me know if it works for you. Let me know via this board.

Barrie
Barrie Davidson

With 5000 rows of data in columns E:G, I recorded the run time for this macro as 47 seconds.

The recorded time for the faster macro that has been proposed (per below) was 00:00:00 - that is, less than one second.

Sub WEIGHT_PLACER_QUARTERLY()
Dim rng As Range
Application.ScreenUpdating = False
Columns(1).Insert
Set rng = Range(Range("F1"), Range("F65536").End(xlUp)).Offset(0, -5)
With rng(1, 1)
.Value = 1
.AutoFill Destination:=rng, Type:=xlFillSeries
End With
rng.Copy rng.End(xlDown).Offset(1, 0)
Range(Cells(1, 1), Cells(1, 1).End(xlDown)).EntireRow.Sort Key1:=rng(1, 1)
Columns(1).Delete
Range(Range("E1"), Range("G65536").End(xlUp)).Cut Range("B2")
End Sub

I'll check it out when I get home tonight (don't have the file here with me).

Regards,
BarrieBarrie Davidson

Oh! Do you doubt the accuracy of what I posted?

Barrie, It works great, however, I did get worried for a bit when all I saw was an hourglass, then suddenly it jammed the junk where it belonged. thanks a ton for all you efforts.

Eddie

Re: Oh! Do you doubt the accuracy of what I posted?

FRANCISCO, THANKS FOR YOUR RESPONSE BUT YOUR MACRO DELETES ROWS 2, 4, 6 ETC.

OK .....

OK. Change it to the revised macro supplied by Omar Sivori at 2488.html

It should still only take about a second to run.

Why do you have so many postings going about the same thing?

No, no...I just want to see what your macro does with the data :)

But ...

, It works great, however, I did get worried for a bit when all I saw was an hourglass, then suddenly it jammed the junk where it belonged. thanks a ton for all you efforts.

OK. But I thought you wanted some fast code (as you requested in one of your other many postings about this macro!). The main point here as far as I'm concerned is not about producing a macro that works - that's not a problem. It's about having a macro that takes about 2 seconds to process 32000 rows of data versus a macro that uses a loop and would take several minutes to procees the same amount of data.
Have a look at Omar Sivori's revised macro.

Slight change .....

The macro I posted doesn't do the same thing as yours. Have a look at the one posted by Omar Sivori at :-
2488.html

The run time should be about the same as for the one I posted.

(Today's slogan :- Ban the Loop!)

BAN THE LOOP

Thanks for your interest Francisco and I do agree that looping is not the most efficient. However, I'll be the first to admit that I'm not a super-VBA'er (yet, but I'm working on it!) and that's all I could come up with. I'll have a look at the posting you mention and I'm sure I'll learn something from it (that's the beauty of this board).

Regards,
Barrie

The macro I posted doesn't do the same thing as yours. Have a look at the one posted by Omar Sivori at :- 2488.html The run time should be about the same as for the one I posted. (Today's slogan :- Ban the Loop!)

I must say that you have a very refreshing, open and positive attitude!

: : The macro I posted doesn't do the same thing as yours. Have a look at the one posted by Omar Sivori at :- : 2488.html : The run time should be about the same as for the one I posted. : (Today's slogan :- Ban the Loop!)

Thanks (nt)

:

TWO POSTINGS

All I did was post two things about two different sets of code that do the same thing. Look what I got, a world record of responses! This board is great.

Two ? .........

Two postings? I made it three - what about 2480.html

You see - nothing escapes the vigilance of this board !