Move row to new sheet and delete - Explain this code line by line

Hi all,

Trying to be resourceful by using code from other posts to move rows from one "parent" sheet to various other sheets, unfortunately making alterations is not as intuitive as I had thought. If I could get some help deciphering this code, I think I could solve my problem and hopefully get better with VBA at the same time:

This is my altered version of some code I copied from a post from late 2017, called "Macro to move rows from one sheet to another based on cell value" (I can't post links )

Please Login or Register  to view this content.

My understanding of what each line does:

1. Names this action as moverows, because that is the objective. Not sure if the empty parentheses are required.
2. "Creates" 3 worksheets, named wi, wp, and wn.
3. "Creates"3 variables, names i, p, and n. Not sure what "R As Range" is doing.
4. Ties the worksheets created in step 2 to the names of the sheets in my workbook. Why are there colons here instead of commas like everywhere else?
5. Not sure. What does the "B" signify? What does xlUp mean? What does "Row To 2 Step - 1" do?
6. In worksheet wi/"Raw", looks for "AABB" in row 1, column 7. I understand how if statements work.
7. Selects cell in row 1 column 1. Not sure about the rest. Somehow it is getting pasted in worksheet wp/"AABB".
8. Deletes the empty row from worksheet wi/"Raw". I know "Shift:=xlUp" is moving the other rows up, but please clarify the inputs.
9. In worksheet wi/"Raw", looks for "A1" in row 1, column 7.
10. Seems similar to step 7, but wi.Rows has replaced wi.Cells and there is no Resize? This part is not working properly.
11. Not sure what this does. A bit confusing because I copied it from another person's project. I just need steps 9 - 12 to replicate the actions taken by steps 6-8.
12. Same as step 9.
13. Repeats steps 1-12 for the next row.

Please clarify whatever you can even if it only one row! All responses are appreciated Also if you have any corrections to my terminology don't hesitate to let me know, I'm (obviously) not fluent in VBA.

Here's my take on 1-8:

1. Names this action as moverows, because that is the objective. The empty parentheses are required.
2. Dimensions 3 worksheets, named wi, wp, and wn.
3. Dimensions 3 long variables, names i, p, and n along with R for a range (object) variable
4. Assigns the worksheets (objects) created in step 2 to the (names of) sheets in my workbook. The colons are to indicate a new line it's a device I use to condense the code
5. From the last non-blank cell in Column B down to the second row (Backward Scan)
6. If the entry in column G is "AABB" for that row
7. assign range R (since R is an object we use “Set”) to that row and cut it and insert it under the header in the AABB sheet.
8. Delete the source row – (Why we used the backward scan so the row numbers don’t get fouled up.)

* I don't remember this one or why all the extra processing at the bottom and I didn't find the workbook in my archives - Orrin

Wow thanks for your help, didn't expect to hear from the original respondent! Using your explanation I took that and wrote this:

Please Login or Register  to view this content.

Not pretty, but proud of myself considering I'd never used VBA before! One issue I'm having is that the routine takes a really long to process... And I'm only running it on 6,000 rows. Eventually I'd like to be able to handle up to 80,000. I was thinking (somewhat arbitrarily) that maybe the constant inserting and deleting of rows was to blame. Since then I have been trying without success to use the cut and paste functions. Can you explain why neither of the following are working?

Instead of :

Please Login or Register  to view this content.

I figured this would work:

Please Login or Register  to view this content.

Or this:

Please Login or Register  to view this content.

But neither do.


On a separate note, if I understand correctly, this routine starts from the bottom most row, so I don't need to worry about deleting the row in sheet wa (Raw). However once I get the cut/paste function working, I'll need some help getting the macro to paste each row below the bottom most row of data when it splits amongst the different sheets.

Realized my mistake of typing out Paste and found that:

Please Login or Register  to view this content.

allows me to cut the bottom row from wa and paste to the next empty row in the "Zipcode Correction" Sheet.

Now that I look at it maybe I should've used:

Please Login or Register  to view this content.

Anyways its working.

New problem, the macro seems to be running marginally faster, but I think the "wa.Cells(a, 1).Resize(1, 9)" part might still be slowing things down. I read somewhere that using the "selection" function was bad for speed. Picking one cell and then resizing seems pretty similar to selecting...

How can I get my macro to cut the bottom row from sheet wa (aka "Raw") using the range function? I would prefer to cut the first 9 columns only, because I have buttons at the top of the sheet and they kept disappearing when initially the macro was deleting rows.

Your post #3 needs code tags - here's how:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

* It's important as the thread could be forbidden until the tags are added!

* a little syntax change

Please Login or Register  to view this content.

Here's code that's a little more compact:

Please Login or Register  to view this content.

*But we're just working on the first 9 columns - if we could dump the whole row then we can write a faster routine