Solution not needed - just clarity on a piece of this code!

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
120
Office Version
  1. 365
Platform
  1. Windows
Hi folks,

I got the below code online somewhere a a few months back and its recently come in handy for something I'm doing (copying every other row and pasting it elsewhere).

However, I'm new to VBA and do not understand 1 thing (which is a problem since I'll need to troubleshoot): why does "Set RowSelect =" appear twice??

I can't wrap my head around why it's Set as MyRange.Rows(2), but then 2 lines later its Set as something else, but in the end it all works perfectly. Don't you Set a variable to an object once and be done with it?

Any explanation on that piece would be super appreciated. Cheers, James.

Sub Copy_every_nth_row()
Dim MyRange As Range, RowSelect As Range, i As Integer, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Set MyRange = Range("A1:A" & LR)
Set RowSelect = MyRange.Rows(2)


For i = 2 To LR Step 2
Set RowSelect = Union(RowSelect, MyRange.Rows(i))
Next i
RowSelect.Copy Range("D2")


End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi James,

The code is building a range called RowSelect. To do this the code uses the VBA only function called UNION which needs a range to already have something in it (this is what your first highlight is for which is Row 2 in this case) before more can be added (appended) to it (this is what the second highlight is doing by adding each Row "i").

Though you define a variable once you don't necessarily only assign something to it once. For instance you could use the variable LR to find the last Row in another column apart from column A as it's presently doing.

HTH

Robert
 
Upvote 0
Thanks Robert for the quick reply and the clear explanation, it definitely answers some of my questions.

The only question I still have is why can't you call the 2nd instance of "Set RowSelect" something else ... like "Set ABC = (Union,RowSelect,MyRange.Rows(i))"?

I just tried it but when I execute only the first cell will gets pasted in col D instead of every 2nd row like its supposed to.

Sorry if this is a really basic question, I thought my fairly good Excel skills would help me in learning VBA, but I'm finding that's not the case :eek:
 
Upvote 0
I'm not too sure what you mean or what you're after (is ABC a range variable??) but here's another slightly different way to do the same that I wrote with some notes (hope it helps):

Code:
Option Explicit
Sub Copy_every_nth_row()

    Dim RowSelect As Range, MyRange As Range
    Dim i As Long, LR As Long
    
    Application.ScreenUpdating = False
    
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Set MyRange = Range("A1:A" & LR)

    For i = 2 To LR Step 2
        'If 'RowSelect' has nothing in it, then...
        If RowSelect Is Nothing Then
            '...start it by adding the first row number to it
            Set RowSelect = MyRange.Rows(i)
        'Else...
        Else
            '...append the next row number to it
            Set RowSelect = Union(RowSelect, MyRange.Rows(i))
        End If
    Next i
    
    RowSelect.Copy Range("D2")
    
    Application.ScreenUpdating = True

End Sub

Robert
 
Last edited:
Upvote 0
Sorry for the confusion (I meant ABC as a variable) .... I was just asking since the 1st variable (in original code) is Set RowSelect=MyRange.Rows(2), why can't the next instance of RowSelect (i.e. RowSelect=Union....) be declared as a differently named variable so it's not as confusing....such as RowSelectABC=Union...?

I get my question probably sounds silly to a more advanced VBA user that just 'gets it', but from a novice like me I can't comprehend why the there needs to be a 2nd instance of RowSelect.

And thanks very much for the alternative code. I'll go through it in the AM with a fresh set of eyes - it'll probably be more intuitive.
 
Upvote 0
You only want to have a single range for ease of output in this case. If you introduce a second range you'll probably have to initially set it and then use UNION to append it. Seems double handling to me.
 
Upvote 0
Ok, that makes sense - I find VBA code super intuitive most times (at least the fundamentals) but times like these its tricky.

Thanks again for your insight on this and sharing a different code, Robert. I'm way ahead on understanding this concept because of your help. Cheers!

James
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top